Performance IF(IF or IF(AND

P

Paul Dennis

I have a large spreadsheet which currently has 4000 * 12 cells in use each
with 3 calculations in nested if, hence 144,000 calc.
e.g.
If (A1 <> "",IF(SUMPRODUCT(--FRED),SUMPRODUCT(--FRED)," ")," ")
..i.e. if A1 is blank then display space or if sum of FRED is 0 then display
space

would it be quicker to use IF(AND, eg.
IF(AND(A1, <> "",SUMPRODUCT(--FRED)),SUMPRODUCT(--FRED)," ")

The reason I'm asking is it's taking a long time to run currently.
 
V

vezerid

Paul,

Is FRED a static or dynamic name? If it is static then you don't have
to calculate it every time, you can just calculate it in a separate
cell, say Z1. Then your formula can become:

=IF(OR(A1="",NOT(Z1))," ",Z1)

If it is a dynamic name, e.g. calculates differently depending on the
cell row then you can use an extra column to calculate the result for
each row and use a similar formula.

Does this help?
Kostis Vezerides
 
P

Paul Dennis

vezerid said:
Paul,

Is FRED a static or dynamic name? If it is static then you don't have
to calculate it every time, you can just calculate it in a separate
cell, say Z1. Then your formula can become:

=IF(OR(A1="",NOT(Z1))," ",Z1)

If it is a dynamic name, e.g. calculates differently depending on the
cell row then you can use an extra column to calculate the result for
each row and use a similar formula.

Does this help?
Kostis Vezerides

Fred is a defined name based on another column on another sheet. Why use another row to do the calculation - would this increase the performance? Which is the faster IF(OR or if(...if
Click to expand...
 
Top