Can you return a blank cell in a formula that isn't text?

Daniel Thornton

Is it possible to return a blank cell which is then part of a formula that adds up with other cells to result in a monetary value?

 

=IF(MILEAGE6 > 50, (MILEAGE6 - 50) * [MILEAGE EXPENSE]$2)

I want values between 0 and 50 to return a blank and not a 0.  When I do the above, it messes up the total column because it thinks the blank cell is a textual value and not a number.  The total column should result in a monetary value.

 

=IF(MILEAGE6 > 50, (MILEAGE6 - 50) * [MILEAGE EXPENSE]$2, 0)

When I have it return a 0, then the formula works but then  I no longer have a blank, but rather a lot of zeros in that column.

 

Any ideas?

 

Thank you very much!

Allie

Hi Daniel,

 

What about going with your second formula, then creating a conditional formatting rule that turns the font color white (or whatever color matches the background color of the cell) if the cell value is 0? This way the cell would appear blank even though it actually contains a 0.

 

I was also wondering about the formulas you're using in the Totals columns. I know SUM and AVG formulas aren't impacted by blank cells, or cells containing text. It would omit those when calculating the sum or average. It's possible that your Totals formulas could be modified so they aren't impacted by the blank cells, but it would depend on how that's set up.

Charles DESCOTES

Have you tried =IF(MILEAGE6 > 50, (MILEAGE6 - 50) * [MILEAGE EXPENSE]$2,"")?

The "" at the end will yield a blank cell. You can also type "-" so you will discretly know that the formula is there and subsequent calculation will wor the same:

 

What do you use to calculate your total column?

Have you tried using =SUM(...) ?

SUM will take "" (or "-") as a zero and make your day.

 

Alternatively, if all these zeros are rather an aethestic problem, use conditional formating to make the font the same color as the background. I personally use a light grey so I discretly know that the formula is there.

 

Did I get your question right?

 

 

Daniel Thornton

I was using the + to add up cells in my total column, but changing to SUM worked!  Thank you Allie and Charles. I changed my formulas where there is no false result, thus returning a blank, and with the total column using SUM, it now shows as a monetary value.  Why didn't I think of that?  Seems kind of obvious now. LOL  =:-)

Allie

Yay! Glad to help :-)