How can I get a formula to ignore " " entries

[mactoolsix]

I have a columns with results = " "
When I add up multiple columns (one containing " ") get a "#value" error.

Is there a way to have the formula ignore " " ?

 

[Joe4]

I have a columns with results = " "

Is it the result of formulas that it is returning " "?
If so, change it so that it returns "" instead of " ".

If that is not possible, please post your formula that will not add up correctly.

 

[mactoolsix]

Yes it is the formula results that return " "
Changed to "" but same problem:

K49=IFERROR(F49+G49+I49+J49,"")
L49=IF(B49<RetYr,L48*(1+Inflation),"")
M49=180,000
N49=K49-L49-M49 returns #VALUE !

Thanks for your help!

 

[Joe4]

Please let me know what is in each and every one of those cell references from your last post. You may have fixed it in one place, but there may be others.
Also, I don't think your second formula came across correctly. See: https://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[mactoolsix]

You're right - okay let's try this

K49=IFERROR(F49+G49+I49+J49,"") returns ""
L49=IF(B49 < RetYr,L48*(1+Inflation),"") returns ""
M49=IF(B49 > = RetYr-1,IF(M48 > 1,M48*(1+Inflation),FirstYrAnnExp)) returns 174,855
N49=K49-L49-M49 returns #VALUE !

RetYr = 2057
Inflation = .03
FirstYrAnnExp = 174,855

 

[Jonmo1]

Make them return 0 instead of "".

There are other methods to 'hide' zeros, like a custom number format or File - Options - Advanced - Uncheck "Show a zero in cells with zero value"

 

[mactoolsix]

Thank you for your time.
I was trying to hide the zeros in that column, I'll play with your suggestions.

Mike

 

[jtakw]

You're right - okay let's try this

K49=IFERROR(F49+G49+I49+J49,"") returns ""
L49=IF(B49 < RetYr,L48*(1+Inflation),"") returns ""
M49=IF(B49 > = RetYr-1,IF(M48 > 1,M48*(1+Inflation),FirstYrAnnExp)) returns 174,855
N49=K49-L49-M49 returns #VALUE !

RetYr = 2057
Inflation = .03
FirstYrAnnExp = 174,855

Hi,

This is just a workaround:

N49=SUM(K49)-SUM(L49)-SUM(M49)

The SUM function will coerce the "" results to 0.

 

[jtakw]

Apparently, this question is ALSO being asked here: https://www.mrexcel.com/forum/excel-questions/1042725-multiple-arguments-multiple-answers.html

 

[mactoolsix]

That works!!

Thanks