Why won't this return a BLANK cell?

[dragonmouse]

I have a formula that adds up information between 6 worksheets. If there is no information greater than ZERO in either cell I want it to be BLANK but it returns 0

=IF(OR(F3>0, O3>0,F3-((IF(SUM('Week 42:Week 52'!P3)>0,SUM('Week 42:Week 52'!P3))))), " ")

Cell F3 is just a standard number, cell O3 is a formula that calculates multiple spreadsheets in the workbook =IF(SUM('Week 42:Week 52'!P3)>0, SUM('Week 42:Week 52'!P3)," "). Cell O3 is friendly and returns a BLANK if "none" of the worksheets happen to have a quantity in that cell.

I'm trying to do a VARIANCE between the quantity that was predicted for the quarter and the amount actually received. Thus the ZERO can be important to determine if they met their predicted delivery or if nothing was projected. That's why I'd prefer it to be blank. Other than the "BLANK", the formula above works fine.

 

[igold]

I can't say for sure why it is returning zero, you may have a stray character in one of your cells. I can say for sure, that you are missing the "Value if False" part of your IF statement.

 

[dragonmouse]

I tried the "function" where I put in the "value if false" and it added:

, " ")

But it's still returning a ZERO. At least for the most part the formula "works" and provides a variance between the two columns, but I'd rather have a BLANK instead of a ZERO.

 

[igold]

Out of curiosity, with the same data that you are using for the IF statement in your OP =IF(OR(F3>0, O3>0,F3-((IF(SUM('Week 42:[Week 52]Week 52'!P3)>0,SUM('Week 42:[Week 52]Week 52'!P3))))), " ") what is the result of this formula.


=SUM('Week 42:[Week 52]Week 52'!P3)

 

[dragonmouse]

Out of curiosity, with the same data that you are using for the IF statement in your OP =IF(OR(F3>0, O3>0,F3-((IF(SUM('Week 42:[Week 52]Week 52'!P3)>0,SUM('Week 42:[Week 52]Week 52'!P3))))), " ") what is the result of this formula.


=SUM('Week 42:[Week 52]Week 52'!P3)

The result is IF one of the worksheet had a value it would return that value. If multiple worksheets had values (in P3 of course) it would sum the all and return the summarized value. This was my "actual" the full "actual" formula in cell O3 on the consolidated sheet is

=IF(SUM('Week 42:Week 52'!P3)>0,SUM('Week 42:Week 52'!P3)," ")

It wouldn't subtract F3 (F3 is simply a number no formula)
from O3 unless I put the formula. It would only give me a #VALUE . Result.

 

[igold]

Have you clicked on the Fx button to the left of the formula bar while your formula is in the bar to see what each part of your formula is returning...