CODEWITHSUNDEEP
excel
gmorissette
gmorissette

Reputation: 291

Subtraction with blank cells - Excel

I created spreadsheet for budgeting purposes and have it set up so that every time I add some data in my income or expenses column, my balance column automatically posts a readjusted value. However, my formula only seems to work for positive values (see conflict on row 4 of example) Here's what I have so far, pasted in as an array formula in B3 and copied down.

=IF((ISNUMBER(C3:D3)),B2+C3-D3,"")

How could improve the "if true" section of my formula?

enter image description here

Upvotes: 2

Views: 6903

Answers (3)

shagans
shagans

Reputation: 232

the problem you're having is your if statement will return false if either C or D is not a number. if(c through d is a number) will mean that it only is true when both are numbers.

If you want to make it so that you only evaluate numbers (and not treat blanks as 0), then you'll probably want to make an OR statement:

=IF(OR(ISNUMBER(C3),ISNUMBER(D3)),B2+C3-D3,"")

Just be aware that if the relative location of B2 is missing data (if you did not have any expense or income the previous day/week/whatever), then the next field will become a value error when you add something. So if B5 is empty because you didn't do any spending or gaining, then when you spend and put value in "expense" on D6, B6 will become #Value.

To fix this, it might be a good idea to finish off not with an empty string, but with the value of the previous date. It's up to you if you want to use it that way.

=IF(OR(ISNUMBER(C3),ISNUMBER(D3)),B2+C3-D3,B2)

Upvotes: 2

Kyle
Kyle

Reputation: 2545

You could put an Or statement in so that if either C3 or D3 is a number, then calculate accordingly. 

=IF(Or(ISNUMBER(C3),ISNUMBER(D3)),B2+C3-D3,"")

Upvotes: 1

IgorKB
IgorKB

Reputation: 38

You could try the following, applied on B3:

=IF(AND(ISBLANK(C3),ISBLANK(D3)),"",B2+C3-D3)

This way, you don't need an array formula - I'm just checking if C3 AND D3 are blank - if so, the cell will be empty, otherwise will do the math.

Upvotes: 0

Related Questions