If formula result is zero, return "" (blank) - Any way to simplify this?

Hi,

This is just a general question about something I come across quite frequently....

When I want to leave a cell blank where the result of the formula would be zero, I tend to find myself using an IF argument to ask if the result is greater than zero, and if so, repeat the formula, if not then ""

If the formula is quite long, it seems a bit silly really, for example...... (And this isn't even what I would call long by any means)...

This:

Formula:

Please Login or Register  to view this content.

Would become this:

Formula:

Please Login or Register  to view this content.

Is there some better way to do this or simplify this kind of situation so that you don't have to repeat the same thing twice?

Perhaps using a different function that im not aware of?

Or if not, would it be possible to create a custom function with vba?


I am interested to hear peoples thought and ideas on this.

Many thanks

I usually just wrap an iferror around the first formula. That way it either calculates or I can tell it to be blank using "". I like iferror because it doesn't double the formula. There may be other ways to handle it too though.

Try this...

=IFERROR(1/(1/SUMIFS(Data!$F$12:$F$23,Data!$B$12:$B$23,$B13,Data!$C$12:$C$23,Input!$J$5,Data!$D$12:$D$23,Input!$J$6)),"")

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.