Simplify formula

[Matt Knight]

Evening everyone, I was urgently trying to get create a formula that
did what I wanted it to do and by trial and error managed to get it.
Only problem is that I think it's far too cumbersome for its own good
and will ultimately slow the performance of my workbook down.

The formula is as follows:
=IF(AND(SUMIF('REVISED ACTIVITY'!$A$11:$A$247,$A12,'REVISED ACTIVITY'!G
$11:G$247),SUMIF('REVISED ACTIVITY'!$B$11:$B$247,$B12,'REVISED
ACTIVITY'!G$11:G$247)),SUMIF('REVISED ACTIVITY'!$A$11:$A
$247,$A12,'REVISED ACTIVITY'!G$11:G$247)*VLOOKUP($A12,'THEATRE USAGE'!
$B$90:$DP$114,HLOOKUP(G$1&$B$9,'THEATRE USAGE'!$D$87:$DO
$88,2,FALSE),FALSE),0)

The VLOOKUP I'm okay with, I think. I could probably INDEX(MATCH())
but the main thing I'm olooking at is the IF,AND,SUMIF parts - I'm
certain here's a more intelligent way of getting the same answer but
can't seem to find it (I did try a sum product with conditions on
Columns A and B but came up short)

Any advice, as always, much appreciated
Matt

 

[ozgrid.com]

While your formula is certainly long I don't see it slowing down
recalculations, which a SUMPRODUCT formula would.

 

[Joe User]

=IF(AND(SUMIF('REVISED ACTIVITY'!$A$11:$A$247,$A12,
'REVISED ACTIVITY'!G$11:G$247),
SUMIF('REVISED ACTIVITY'!$B$11:$B$247,$B12,
'REVISED ACTIVITY'!G$11:G$247)),
SUMIF('REVISED ACTIVITY'!$A$11:$A$247,$A12,
'REVISED ACTIVITY'!G$11:G$247)
*VLOOKUP($A12,'THEATRE USAGE'!$B$90:$DP$114,
HLOOKUP(G$1&$B$9,'THEATRE USAGE'!$D$87:$DO$88,2,FALSE),
FALSE),0)

First, if you can sort the lookup row and column for the HLOOKUP and VLOOKUP
respectively, you could change FALSE to TRUE and allow Excel to do a binary
search. No harm in allowing for the less stringent match condition that
TRUE permits. Presumably you expect an exact match; otherwise, your formula
would result in an error -- not good design.

Second, you can avoid doing the SUMIF(A11:A247) twice and, ergo, the AND().
The following should be functionally equivalent:

=IF(SUMIF('REVISED ACTIVITY'!$B$11:$B$247,$B12,
'REVISED ACTIVITY'!G$11:G$247),
SUMIF('REVISED ACTIVITY'!$A$11:$A$247,$A12,
'REVISED ACTIVITY'!G$11:G$247)
*VLOOKUP($A12,'THEATRE USAGE'!$B$90:$DP$114,
HLOOKUP(G$1&$B$9,'THEATRE USAGE'!$D$87:$DO$88,2,FALSE),FALSE),0)

Note that when the SUMIF(A11:A247) is zero, SUMIF(A11:A247,...)*VLOOKUP(...)
is zero.

The trade-off is that you will be doing the HLOOKUP and VLOOKUP
unnecessarily when SUMIF(A11:A247) is zero. Whether that is slower or
faster overall depends on the relative frequency that SUMIF(A11:A247) is
zero, something only you can determine. But note that AND() evaluates both
parameters, even if the first parameter returns zero (FALSE). Also, the
first suggestion above significantly reduces the cost of the HLOOKUP and
VLOOKUP.


----- original message -----

 

[Matt Knight]

Thanks Joe - last night the sheer blur of spreadsheets and formulae
left me a little overwhelmed! Anything which reduces the amount of
work Excel has to do is worthwhile me doing, seeing as how my model
will be running several thousand calculations - the simpler it is
before I start throwing in macros to do some work, the better!

Cheers
Matt

 

[Dana DeLouis]

the sheer blur of spreadsheets and formulae

left me a little overwhelmed!

I would just add that the use of Range Names instead of cell references
might be a little easier on the eyes.

HTH
Dana DeLouis