Shortened IF statement to avoid repetitive calculation?

**gksa** · 07-12-2011, 02:20 PM

I've always wondered... isn't there a shortened way to achieve the same goal as the following?

Please Login or Register  to view this content.

(Assume x is a long, complicated formula).

If the value of the calculation is not 0, it really seems unnecessary to calculate it again. I realise I could do the initial calculation in a separate cell, then just use that value again, but I was just wondering if there's another formula or IF syntax that will accomplish the same thing in a single cell.

Thanks in advance!

**MarvinP** · 07-12-2011, 02:31 PM

Hi gksa,

In the newer versions of Excel there is an IfError() function that may do what you are thinking.

http://www.excelfunctions.net/Excel-Iferror.html

**NBVC** · 07-12-2011, 02:41 PM

You can format the result cell so that if the result is 0, it is hidden

use formula

=X

and format as Custom: 0;-0;;@

**gksa** · 07-12-2011, 02:52 PM

Hmmm... I know about IFERROR - I was hoping there was some version of it to use for something other than errors!

As a rule, I try to use Conditional Formatting sparingly, because it seems to slow things down even more than VLOOKUP. But using a number format to hide the 0s could work very well in most cases, where the concern is merely with visual matters - thanks NBVC!

Does anyone have any ideas though, for the cases where merely changing the display wouldn't cut it?

Edit: I have spotted something along this line somewhere, but for the life of me I can't find it again. I seem to recall that the person had used syntax similiar to column() (which refers to the current column the cell is in without using an actual cell reference) in an IF statement, but that doesn't seem to work. Now I don't know whether they were just writing out the formula in shorthand, or not.