I am having the IF function evaluate a row of numbers for the last cell
populated with a number greater than 0 (zero). When found it is performing a
calculation based on the number entered in to the last cell of the row
populated.

=IF(J410,J41-(I5*J5),IF(I410,I41-(I5*J5),IF(H410,H41-(I5*J5),IF(G410,G41-(I5*J5),IF(F410,F41-(I5*J5),IF(E410,E41-(i5*j5)....

I must evalaute cells p41..b41 for the last cell populated 0, and basis for
the calculation. I think I hit the max on nested arguments. I read that
Lookup is and option, but just do not get it. Or, can I work somehow around
the 7 argument limit. Thanks for your insight. Let me know if you need to
know more for recommendation purposes. Tom




--
Sony Luvy

Hi Tom

from what i can tell you want to find the last non-zero in a range (B41:P41)
and when you find the value subtract (I5*J5) from it.

If so, this formula should work for you
=LOOKUP(2,1/(-B41:P41<0),B41:P41)-(I5*J5)

Cheers
JulieD

"sony654" wrote in message
...
I am having the IF function evaluate a row of numbers for the last cell
populated with a number greater than 0 (zero). When found it is
performing a
calculation based on the number entered in to the last cell of the row
populated.

=IF(J410,J41-(I5*J5),IF(I410,I41-(I5*J5),IF(H410,H41-(I5*J5),IF(G410,G41-(I5*J5),IF(F410,F41-(I5*J5),IF(E410,E41-(i5*j5)....

I must evalaute cells p41..b41 for the last cell populated 0, and basis
for
the calculation. I think I hit the max on nested arguments. I read that
Lookup is and option, but just do not get it. Or, can I work somehow
around
the 7 argument limit. Thanks for your insight. Let me know if you need
to
know more for recommendation purposes. Tom




--
Sony Luvy

JulieD wrote:
Hi Tom

from what i can tell you want to find the last non-zero in a range (B41:P41)
and when you find the value subtract (I5*J5) from it.

If so, this formula should work for you
=LOOKUP(2,1/(-B41:P41<0),B41:P41)-(I5*J5)

[...]

Agree. Just to avoid accidents with text and neg numbers...

=LOOKUP(2,1/((B41:P410)*ISNUMBER(B41:PK1)),B41:P41)-(I5*J5)

Hi Aladin

just a slight correction
=LOOKUP(2,1/((B41:P410)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5)

(mis-type of cell reference in the ISNUMBER function)

Cheers
JulieD

"Aladin Akyurek" wrote in message
...
JulieD wrote:
Hi Tom

from what i can tell you want to find the last non-zero in a range
(B41:P41) and when you find the value subtract (I5*J5) from it.

If so, this formula should work for you
=LOOKUP(2,1/(-B41:P41<0),B41:P41)-(I5*J5)

[...]

Agree. Just to avoid accidents with text and neg numbers...

=LOOKUP(2,1/((B41:P410)*ISNUMBER(B41:PK1)),B41:P41)-(I5*J5)

Hi Aladin

just another note - the formula i initially supplied seems to work fine if
there is text in the range (ie it ignores it) - am i missing something.

Cheers
JulieD

"Aladin Akyurek" wrote in message
...
JulieD wrote:
Hi Tom

from what i can tell you want to find the last non-zero in a range
(B41:P41) and when you find the value subtract (I5*J5) from it.

If so, this formula should work for you
=LOOKUP(2,1/(-B41:P41<0),B41:P41)-(I5*J5)

[...]

Agree. Just to avoid accidents with text and neg numbers...

=LOOKUP(2,1/((B41:P410)*ISNUMBER(B41:PK1)),B41:P41)-(I5*J5)

"JulieD" wrote...
just another note - the formula i initially supplied seems to work fine if
there is text in the range (ie it ignores it) - am i missing something.
....

If there's text that looks like a valid number below the last true number in
the range, your formula would return that numberic text. Also, OP asked for
last *positive* number, while your formula returns the last number, be it
positive, negative or zero.

Hi Harlan

thanks for the clarification - i tested my formula on "text" not text that
looks like a valid number. Understood about the negative numbers too.

Cheers
JulieD

"Harlan Grove" wrote in message
...
"JulieD" wrote...
just another note - the formula i initially supplied seems to work fine if
there is text in the range (ie it ignores it) - am i missing something.
...

If there's text that looks like a valid number below the last true number
in
the range, your formula would return that numberic text. Also, OP asked
for
last *positive* number, while your formula returns the last number, be it
positive, negative or zero.

Harlan,

I saw your post appear then suddenly disappear. As if it had been deleted
from the server.
This just recently happened to one of my posts too. Do you know what is
going on?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harlan Grove" wrote in message
...
"JulieD" wrote...
just another note - the formula i initially supplied seems to work fine if
there is text in the range (ie it ignores it) - am i missing something.
...

If there's text that looks like a valid number below the last true number
in
the range, your formula would return that numberic text. Also, OP asked
for
last *positive* number, while your formula returns the last number, be it
positive, negative or zero.