Excel - if 0 then leave blank?

[NeoGen 10]

I'm just uing the simple =SUM(E22*F22) equation, but I want it left blank if nothing is entered in to the E22 and F22 boxes, anyone got a suggestion?

 

Many thanks

 

N.

[fnkysknky 10]

Not got access to Excel at the moment but I'm sure there's an IsEmpty or IsBlank (there's a few IsXxx functions) function that could be used in IF statements...

[NeoGen 10]

I've looked at all the formulas and nothing is jumping out to me that will allow a blank cell if the result is 0.

[fnkysknky 10]

What about

 

=IF((AND(ISBLANK(E22), ISBLANK(F22)), SUM(E22*F22), "")

 

I don't actually have a way of checking it at the moment so there may be a syntax error somewhere...

[cgksheff 44]

From top menu:

 

>Tools > Option > View > Uncheck "Zero values"

[max 13]

Pretty much there

 

=IF(OR(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))

 

will give a blank if either e22 or f22 is blank

 

=IF(AND(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))

 

will give a blank if both e22 and f22 is blank but a zero if one or other has a value

[fnkysknky 10]

Oops, wasn't thinking properly when I wrote that it would seem

[NeoGen 10]

Thanks guys for your input, what I did in the end was:

 

Go to 'Format'

 

Then 'Conditional Formatting'

 

In Condition 1 I selected 'Cell Value Is'

 

Then set it to 'Equal To'

 

And entered '0'

 

Then clicked on format and set the font colour to white and clicked on 'OK then 'OK'

 

Might not be the best way but it does exactly what I want, when there is a 0 it does not show anything.

 

Thanks

 

N

[cgksheff 44]

What's wrong with my way?

 

Too simple?

[NeoGen 10]

What's wrong with my way?

 

Too simple?

 

Not sure if it's because I'm using the latest version on a Mac, but if I go to 'Tools' there is no 'Options' there, so I got tripped at the first hurdle.

[cgksheff 44]

I'm still on 2003!

 

Excel 2007 advice is here:

 

http://office.microsoft.com/en-us/excel/HP100704611033.aspx

[barleycorn 10]

I'm just uing the simple =SUM(E22*F22) equation, but I want it left blank if nothing is entered in to the E22 and F22 boxes, anyone got a suggestion?

 

Many thanks

 

N.

A colleague has just asked me a very similar question. After playing about with IF functions I just Googled it and this was top hit...

Pretty much there

 

=IF(OR(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))

 

will give a blank if either e22 or f22 is blank

 

=IF(AND(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))

 

will give a blank if both e22 and f22 is blank but a zero if one or other has a value

 

... and this works perfectly, thank you. Saved me the hassle of working it out myself.

 

jb