Log In
Register
Home
April 23, 2015
Offline
Hi there,
I think I am getting lazy posting here and not figuring some of these out for myself but regardless I would really appreciate someone solving this one for me. In the attached book I have an entry in cell AF1 adding the values in cells AF16 + AF21. The result is a #value error. If I put a number into AF21 the addition works and #value disappears. I have tried changing the format of AF21 but that made no difference.
Can someone help?
Alan
VIP
April 21, 2015
Offline
Hi Alan, when I put a number in AF21 it counts in AF1, as you also discovered. When I then delete the number in AF21, it still counts the value in AF16. I don't know if that is what you are looking for? Otherwise you have to add something like IF(AF21<>"",AF16+AF21,""). That make it look a little better I think.
Frans
VIP
Trusted Members
December 7, 2016
Offline
Hello Alan,
You are getting this #VALUE! error because you are trying to add a number value (cell AF16) to a text value (cell AF21, as the result of the formula is "" it is text). If you instead of using "" as a result for false use 0, then you will see a correct result in cell AF1.
April 23, 2015
Offline
Hi Frans,
Thanks for the reply, the formula you suggested does work but on the sheet instead of displaying the #value error its displaying the word FALSE instead. I guess that is the correct result of the formula but I don't want anything displayed in the cell adding the values except for the actual value. If there are no values in any of the cells in the formula then the cell should simply be blank.
If you look in the attached you will see I added "Sheet1" with the same type of calculation shown in cell E1, this one behaves the way I want it to and the way I always thought Excel would behave, so I am thinking there is something on the WL Gantt sheet that is changing how the formulas work/display. Any ideas?
Alan
VIP
Trusted Members
June 25, 2016
Offline
You could modify the formula to check if the cell is blank before adding.
=AF16+IF(AF21="",0,AF21)
But I would prefer your formula in AF21 to show =IF(AF$19=1,$B21,0) like what Anders suggested.
You can then format AF21 to hide the 0.
1 Guest(s)
