MegaFormula curiosity

[Sige]

Hi There,


Underneath formula with helper cell (or defined name) suits its
purposes!
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1&":"&ADDRESS(Maxi;3;1;1)=C2))
with
Maxi=MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";255);A:A));MATCH(REPT("z";255);A:A);0);MATCH(9,99999999999999E+307;A:A)))

Though combining it: i.e. No helper cell! ....
=SUMPRODUCT(--(INDIRECT(ADDRESS(2;3;1;1&":"&ADDRESS(MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";255);A:A));MATCH(REPT("z";255);A:A);0);MATCH(9,99999999999999E+307;A:A)));3;1;1)=C2))

Excel tells me there is an error?!
This puzzles me.
Is it maybe because you cannot nest more than 7 functions ... like you
cannot nest more than 7 IF's?

Any insight, highly appreciated, Sige

 

[Niek Otten]

<Is it maybe because you cannot nest more than 7 functions ... like you
cannot nest more than 7 IF's?>

The 7 nested IFs are often mentioned, but in fact it is simply 7 nested
levels of functions. IF or otherwise.

 

[Sige]

Hi Niek,

Sounds logic!

Thanks Sige