Jump to content
We’re excited to announce the forum is under new management! Click here for details.

Excel - if 0 then leave blank?

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

From top menu:

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

What's wrong with my way?

 

Too simple?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.