Aladin Akyurek said:
Steved said:
Hello Aladin from Steved
Thankyou I personally would not do it as I believe the less formulas the
better.
Just speaking for myself.
[...]
Really?
I am perfectly sure that Aladin does not need me to talk for him and
probably his reply says much more than I am about to, but the number of
calculations in a formula is not always apparent at face value.
I stand to be corrected in this and if I am shot down in flames it will only
serve to increase my understanding of XL
If we take the formula that Regdyer gave (cut down to make it manageable)
=SUMPRODUCT(($A$4:$A$6=1)*($B$4:$B$6="P")*D4:G6) as an
example it, it looks like it has 3 evaluations in Column A + 3 evaluations
in
Column B+ 4 * 3 calculations in Columns G to G making a total of 18
calculations.
However, if we highlight (($A$4:$A$6=1) and press f9 we see
{True;True;True;True} as does ($B$4:$B$6="P"). D4:G6 produces
{ValueD4,ValueE4,ValueF4,ValueG4;
ValueD5,ValueE5,ValueF5,ValueG5;
ValueD6,ValueE6,ValueF6,ValueG6}
So when this is evaluated we get:
1*1*ValueD4 , 1*1*ValueE4 , 1*1*ValueF4 , 1*1*ValueG4 ;
1*1*ValueD5, ......but wait a minute where did those 1*1's come from? we
only had one set of TRUE's in each bracket and we have already used them.
Surely Columns A & B have to be evaluated again to provide the extra sets of
TRUE's.
If so then the true total of calculations is:
3 * 4 evaluations of Column A + 3 * 4 evaluations of Column B + 3 * 4
calculations in Columns D to G making a total of 36 calculations.
Aladin's suggestion uses 3 SUM calculations in Column H + 3 evaluations
in Column A + 3 evaluations in Column B + 3 calculations of the SUMs in
Column H making a total of 12 calculations - 1/3 of the original number!
So which solutuon has the fewer calculations?
--
Regards
Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
109 but if I change it to D4:G109 it shows #VALUE!