IF statement w/ more than 7 arguments needed

[Guest]

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(J41>0,J41-(I5*J5),IF(I41>0,I41-(I5*J5),IF(H41>0,H41-(I5*J5),IF(G41>0,G41-(I5*J5),IF(F41>0,F41-(I5*J5),IF(E41>0,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

 

[JulieD]

Hi Tom

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

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

Cheers
JulieD

 

[Aladin Akyurek]

Hi Tom

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

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

[...]

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

=LOOKUP(2,1/((B4141>0)*ISNUMBER(B41K1)),B4141)-(I5*J5)

 

[JulieD]

Hi Aladin

just a slight correction
=LOOKUP(2,1/((B4141>0)*ISNUMBER(B4141)),B4141)-(I5*J5)

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

Cheers
JulieD

Hi Tom

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

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

[...]

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

=LOOKUP(2,1/((B4141>0)*ISNUMBER(B41K1)),B4141)-(I5*J5)

 

[JulieD]

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

Hi Tom

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

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

[...]

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

=LOOKUP(2,1/((B4141>0)*ISNUMBER(B41K1)),B4141)-(I5*J5)

 

[Harlan Grove]

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.

 

[JulieD]

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

 

[Guest]

Julie, Aladin, Harlan: Thankyou very much. Now when no cells are populated
with a number >0, it returns #N/A. What can I add to return "0", if no cells
are populated. Thanks for everything on this . Tom

 

[Harlan Grove]

Julie, Aladin, Harlan: Thankyou very much. Now when no cells are
populated with a number >0, it returns #N/A. What can I add to return
"0", if no cells are populated. Thanks for everything on this . Tom

=IF(COUNTIF(Range,">0"),<original_formula_here>,0)

 

[Rob van Gelder]

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?

 

[Ken Wright]

Seen it happen a couple of times myself on other peoples posts as well in
the last day or so.

 

[JulieD]

it happened to me the other night - so i took it as a sign that i had spent
too long on the ngs

 

[Ken Wright]

I have my wife to give me those signs, and she is very good at them

 

[Guest]

Harlan, Julie, Aladin, Rob, Ken:
Below is the formula I am now using (thanks to the group input). How do I
update to return "0", as opposed to N/A#, if all cells B4141 are empty.

=LOOKUP(2,1/((B4141>0)*ISNUMBER(B4141)),B4141)-(I5*J5)

Thanks - Tom
==================================================

 

[Harlan Grove]

Below is the formula I am now using (thanks to the group input). How do I
update to return "0", as opposed to N/A#, if all cells B4141 are empty.

=LOOKUP(2,1/((B4141>0)*ISNUMBER(B4141)),B4141)-(I5*J5) ....

Reread my reply immediately above and try to figure it out. Maybe I need to
make explicit that you'll need to replace Range with the range you're
checking, and you shouldn't include the leading = in
<original_formula_here>.

 

[Guest]

Harlan - Thankyou very much. Your advise is right on. Thans again - Tom