Inefficient formula...

[MacBookProUser]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Some of my formulas have to check for an DIV/0 error or other error conditions. Therefore, I've been using ISERROR in conjunction with an IF statement, however, the logical test is also the value. Is there a way to simplify the formula so I don't have to duplicate the expression twice?

IF(ISERROR(AVERAGEIFS('Working Area'!V:V,'Working Area'!$U:$U,MONTH($B$2), 'Working Area'!$B:$B,"Not registered",RegUsers!$T:$T,"Canada")), 0, AVERAGEIFS('Working Area'!V:V,'Working Area'!$U:$U,MONTH($B$2), 'Working Area'!$B:$B,"Not registered",RegUsers!$T:$T,"Canada"))

My approach above makes the formula inefficient and since I'm calculating a large number of these, it takes a significant amount of time.

Many thanks.

 

[CyberTaz]

One option without getting intense ;

Put your Average function somewhere else [maybe even on a different sheet],
let's say XX1000. Then where you want the result displayed just use:

= IF(ISERROR(XX1000),0,XX1000)

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Niek Otten]

I think Excel2008 has all the Excel2007 for Windows formulas, so look at the
IFERROR (not ISERROR!) function.

 

[CyberTaz]

Good point, Niek- and yes, it is there. Never gave that a thought

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

Some of my formulas have to check for an DIV/0 error or other error
conditions. Therefore, I've been using ISERROR in conjunction with an IF
statement, however, the logical test is also the value. Is there a way to
simplify the formula so I don't have to duplicate the expression twice?

IF(ISERROR(AVERAGEIFS('Working Area'!V:V,'Working Area'!$U:$U,MONTH($B$2),
'Working Area'!$B:$B,"Not registered",RegUsers!$T:$T,"Canada")), 0,
AVERAGEIFS('Working Area'!V:V,'Working Area'!$U:$U,MONTH($B$2), 'Working
Area'!$B:$B,"Not registered",RegUsers!$T:$T,"Canada"))

My approach above makes the formula inefficient and since I'm calculating a
large number of these, it takes a significant amount of time.

Niek's suggestion to use IFERROR() works fine, and for your application
may work fine. However, it's a blunt tool (as is ISERROR()), which will
ignore not only the error you're trying to avoid, but also any others.
For instance, using either ISERROR() or IFERROR(), e.g.,:

=IFERROR(A1/B1,"Div by zero")

will return "Div by zero" if B1=0, or if A1=#VALUE! or, if A1=J1*2, you
later delete column J so that A1 returns #REF!

In the latter two cases, you or your users are given incorrect
information.

In good spreadsheet design, you're better off eliminating the *expected*
error conditions (and, if desired, trapping the rest), so that you're
not led down the primrose path if there's a real error in your data.

In the above case, I might use a defined name to COUNTIFS() the number
of numbers in your range, and use

=IF(Count_of_my_range = 0, 0, AVERAGEIFS(...))

COUNTIFS is a tiny bit faster than AVERAGEIFS, but OTOH, if you have
several formulae with the same criteria, but different sum ranges, the
defined name gets calculated only once, so you should see a substantial
speed improvement.

As an alternative, for your specific situation, you could use this
instead:

=SUMPRODUCT(--('Working Area'!U:U = MONTH($B$2)), --('Working
Area'!B:B = "Not Registered"), --(RegUsers!T:T = "Canada"), 'Working
Area'!V:V)

which will return 0 if no (combined) criteria are met.

 

[MacBookProUser]

Thank you all for your suggestions.

J.E.-- Two questions for you...

1. In the "defined name to CountIFS()" suggestion, are you implying the defined name can contain the function also?

Your IF(Count_of_my_range=... would appear to indicate this but I wasn't aware of this capability.

2. Can you explain the use of "--" in the SUMPRODUCT function.

Thanks again.

 

[JE McGimpsey]

Thank you all for your suggestions.

J.E.-- Two questions for you...

1. In the "defined name to CountIFS()" suggestion, are you implying the
defined name can contain the function also?

No, I wasn't implying at all - I was stating explicitly that the defined
name can contain a function.

Your IF(Count_of_my_range=... would appear to indicate this but I wasn't
aware of this capability.

Using functions in defined names is a powerful way to minimize
repetitive calculations without the use of "helper cells".

In addition, the use of relative addressing can be extremely powerful.
For instance, if you select cell B1 in Sheet1, choose Insert/Name/Define
and enter:

Names in workbook: cell_to_my_left
Refers to: =!A1

then you can enter this in cell B1:

=cell_to_my_left * 2

and twice the value in A1 will be returned. If you enter this in Sheet2,
cell K10, however:

=cell_to_my_left + 1

then K10 will display the value of Sheet2!J1 plus one.

In order to *always* refer to Sheet1!A1, you need to use

Refers to: =Sheet1!$A$1

2. Can you explain the use of "--" in the SUMPRODUCT function.

See

http://www.mcgimpsey.com/excel/doubleneg.html