How do I get IF Formula to show a blank output

February 5, 2020 at 09:42:44
Specs: Windows 10
I have tried the below formula. It will show a blank under my column for balance (the column i'm putting this formula in) which is what I want. I have to enter a number in under both my columns for deposit and withdraw to get it to work, which is not what I want. I want the formula to work by entering in a number in either, but not both and show a blank if nothing is entered. Please help!

=IF(ISBLANK([@DEPOSIT]), "", IF(ISBLANK([@WITHDRAW]), "", (E19)))


See More: How do I get IF Formula to show a blank output


#1
February 5, 2020 at 10:19:15
re: I want the formula to work by entering in a number in either, but not both and show a blank if nothing is entered.

In other words, the result should be blank in 2 cases: Entries in both columns or entries in neither.

Is that correct?

If so, one of many ways to accomplish your goal is this:

=IF(COUNT(table_name[@[Deposit]:[Withdraw]])=1, E19,"")

(Replace table_name with the actual name of your table.)

I hope that helps!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#2
February 5, 2020 at 11:34:01
That did help. It was spot on. I really appreciate the help. Unfortunately, i was thinking clearly and now my balance will always change, even the past balances...whoops. So what i'm trying to do now is to come up with a formula that will work for me that will not alter the past balance. For example, I start of with a balance of 100, if I enter a dollar amount under deposit or withdraw my new balance should reflect that without changing my previous balance (and of course if nothing is entered the balance should show blank). Below is what I tried, but no success.


=IF(COUNT(CheckRegister[@[DEPOSIT]:[WITHDRAW]])=1, E8+[@DEPOSIT]:E8-[@WITHDRAW],"")

E8 = past balance


Report •

#3
February 5, 2020 at 13:20:34
Please keep in mind that we can't see your workbook from where we are sitting, so we don't know how your spreadsheet is laid out.

Based on your words, I am assuming (always dangerous) that your table looks something like this:

            C          D           E    
7        Deposit    Withdraw    Balance
8                                 100
9

If that is correct, I am further assuming (dangerous again) that you will now enter a Deposit or Withdraw value in Row 9, with the goal of showing the new balance in E9.

If that is correct, my final assumption (dangerous still) is that both the Deposit and Withdraw values are entered as positive numbers.

If all of that is correct, then try this formula in E9:

=IF(COUNT(CheckRegister[@[Deposit]:[Withdraw]])=1, E8+SUM([@Deposit],[@Withdraw]*-1),"")


Here are my results, based on the assumptions I made above:

            C          D           E    
7        Deposit    Withdraw    Balance
8                                 100
9                     6            94

==============================

            C          D           E    
7        Deposit    Withdraw    Balance
8                                 100
9           5                     105

==============================

            C          D           E    
7        Deposit    Withdraw    Balance
8                                 100
9           5          6            


message edited by DerbyDad03


Report •

Related Solutions

#4
February 5, 2020 at 13:31:42
You assumed CORRECTLY. I was worried that I didn't explain it well, but you got it. Thank you so much for your help. It works perfectly!

Report •

#5
February 6, 2020 at 07:09:04
BTW...this works also. Save yourself 2 keystrokes. :-)

=IF(COUNT(CheckRegister[@[Deposit]:[Withdraw]])=1, E8+SUM([@Deposit],-[@Withdraw]),"")


message edited by DerbyDad03


Report •

#6
February 6, 2020 at 11:39:46
It is curious that you want the result in Balance when there is
either a Deposit or Withdrawl, but not when there are both.
Is that really what you want? It just seems odd.

-- Jeff, in Minneapolis


Report •

#7
February 6, 2020 at 12:10:36
My guess is that the sheet looks more like this than the example my previous post:


        A         B             C          D           E    
7      Date   Description    Deposit    Withdraw    Balance
8                                                     100
9

One entry per line so that a description can be entered.

You don't necessarily need to deal with "both" in the formula if you'll never enter a value in both columns at once, but if you accidentally did, you'd get a blank in the Balance column which would really stand out.

message edited by DerbyDad03


Report •

#8
February 6, 2020 at 15:18:00
Thanks for taking the time to do that. It looks like a
very reasonable guess!

-- Jeff, in Minneapolis


Report •

Ask Question