I have a Calc spreadsheet where I allocate amounts using an IF formula as here:
=IF($Y$3=$G66,$E66,"")
I sometimes need to be able to add or subtract numbers from the cell.
If the formula returns a number then it works. If the formula returns blank, then I get a #VALUE! error message. Is there any way to get the blank cell to allow the adding of a number as here?
=IF($Y$3=$G66,$E66,"")+5
Hallo
=IF($Y$3=$G66 ; $E66+5 ; "")
or maybe ??
=IF($Y$3=$G66 ; $E66+5 ; 5)
1 Like
In such a case where a condition value may be modified, I proceed in two steps, separating “generation” from usage.
“Intermediate” values are stored in a dedicated column (or row) which can be hidden if needed as
=IF($Y$3=$G66, $E66, 0)
These “intermediate” cells receive a special format 0;-0,"" if you want to blank out zero values.
Then you can use “intermediates” without caring for blanks because they are now zero numerical values. Assuming the “intermediate” are in column H, you simply enter
=$H66 + 5
A solution, unless anyone can provide a different one.
So in the formula, I replaced “” with 0 (zero) and then used the “hide cells with zero” function.