Why won't this return a BLANK cell?

dragonmouse

Board Regular
Joined
May 14, 2008
Messages
129
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top