Alternative to =IF(ISNUMBER(A1),A1,"")

[Nightlytic]

Hi,

I often form my formulas with the logic of arriving at a number/value, then when I get something I didn't want, for example an empty value, or a 0, I want it to say "" instead, or maybe a specific text. For example:
=IF(SUMIFS(A:A,B:B,C1)<50,"Irrelevant",SUMIFS(A:A,B:B,C1))

Above sums up range A:A if range B:B matches cell C1, of the sum is less than 50, dismiss as irrelevant, type Irrelevant in the output. If not, move to false argument and return the actual number it found.

My problem as follows: This means that, if a number is returned, it does each calculation twice.
=IF(SUMIFS(A:A,B:B,C1)<50,"Irrelevant",SUMIFS(A:A,B:B,C1))
1 and 2, and sometimes these can be very long strings, this is terrible for efficiency, and difficult for my colleagues to follow.

Is there a way to cut down on the calculation time, or make it 'cleaner'?

Regards

 

[bosco_yip]

Perhaps,

In D1, enter :

=SUMIFS(A:A,B:B,C1)

and, D1 >> Format Cells >> Number >> Custom, in the Type box enter :

[<50]"Irrelevant";0

Regards
Bosco

 

[NARAYANK991]

Hi ,

The standard technique to use is the IFERROR function , which does the calculation only once.

However , the IFERROR function will take the alternative action you want only if the result of the calculation is an error.

If you can generate an error in some way or the other , then this technique will work.

As an example , suppose your formula is :

=IF(expression = 0 , "Irrelevant" , expression)

The way to generate an error from this is to take the reciprocal , as follows :

=IFERROR(1/ (1/expression) , "Irrelevant")

Here the parentheses highlighted in red are crucial ; in their absence the formula will not work when expression is non-zero , since Excel in going from left to right will divide 1/1 and the actual expression which will be evaluated is 1/expression , which is not what we want.

With the parentheses , Excel first takes the reciprocal of expression , and if expression is non-zero , will get 1/expression ; now the outer divide operation is carried out to get back expression.

Thus , this technique is most often used when testing for an expression being zero or non-zero.

I doubt it can be used in other cases.

Narayan'

 

[Nightlytic]

Ah clever.
Is there an excel equivalent of a memory button on a calculator?
It honestly just bugs me that it recalculates the same thing again, I wish I could declare it like in VBA and use it as a constant.

 

[NARAYANK991]

Hi ,

That is exactly one of the functions of named ranges.

If you can create a named range for the particular calculation that you wish to use again and again , then all your formulae can refer to the named range instead of the expression that is to be calculated.

Narayan