IF statement w/ more than 7 arguments needed

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




--
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" <sony654@discussions.microsoft.com> wrote in message
news:F71F3215-EA5C-40AE-9C0F-35AD9B6DE75E@microsoft.com...
>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
>
>
>
>
> --
> 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:P41>0)*ISNUMBER(B41:PK1)),B41:P41)-(I5*J5)

Hi Aladin

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

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

Cheers
JulieD

"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:41FD0D4E.6050105@xs4all.nl...
> 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:P41>0)*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" <akyurek@xs4all.nl> wrote in message
news:41FD0D4E.6050105@xs4all.nl...
> 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:P41>0)*ISNUMBER(B41:PK1)),B41:P41)-(I5*J5)

"JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.

In that case, this small change should get the answer:

=LOOKUP(2,1/(-B41:P41<0),B41:P41)-(I5*J5)

- Mangesh

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" <hrlngrv@aol.com> wrote in message
news:%23m4Ke41BFHA.2392@TK2MSFTNGP14.phx.gbl...
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.
>
>

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

"JulieD" wrote:

> 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" <hrlngrv@aol.com> wrote in message
> news:%23m4Ke41BFHA.2392@TK2MSFTNGP14.phx.gbl...
> > "JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.
> >
> >
>
>
>

"sony654" <sony654@discussions.microsoft.com> wrote...
>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)

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" <hrlngrv@aol.com> wrote in message
news:%23m4Ke41BFHA.2392@TK2MSFTNGP14.phx.gbl...
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.
>
>

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Rob van Gelder" <newsgroups@nojunkmail-vangelder.co.nz> wrote in message
news:eZYVkdDCFHA.3592@TK2MSFTNGP09.phx.gbl...
> 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" <hrlngrv@aol.com> wrote in message
> news:%23m4Ke41BFHA.2392@TK2MSFTNGP14.phx.gbl...
> > "JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.
> >
> >
>
>

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" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23MOwVIJCFHA.3236@TK2MSFTNGP15.phx.gbl...
> Seen it happen a couple of times myself on other peoples posts as well in
> the last day or so.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Rob van Gelder" <newsgroups@nojunkmail-vangelder.co.nz> wrote in message
> news:eZYVkdDCFHA.3592@TK2MSFTNGP09.phx.gbl...
>> 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" <hrlngrv@aol.com> wrote in message
>> news:%23m4Ke41BFHA.2392@TK2MSFTNGP14.phx.gbl...
>> > "JulieD" <JulieD@hctsReMoVeThIs.net.au> 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.
>> >
>> >
>>
>>
>
>

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>

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 B41:P41 are empty.

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

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

"Harlan Grove" wrote:

> "sony654" <sony654@discussions.microsoft.com> wrote...
> >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)
>
>
>

=IF(COUNTA(B41:P41)>0,LOOKUP(2,1/((B41:P41>0)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5),0)

- Mangesh

"sony654" <sony654@discussions.microsoft.com> wrote...
>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 B41:P41 are empty.
>
>=LOOKUP(2,1/((B41:P41>0)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5)
....
>"Harlan Grove" wrote:
>>"sony654" <sony654@discussions.microsoft.com> wrote...
>>>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)

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>.

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

"Harlan Grove" wrote:

> "sony654" <sony654@discussions.microsoft.com> wrote...
> >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 B41:P41 are empty.
> >
> >=LOOKUP(2,1/((B41:P41>0)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5)
> ....
> >"Harlan Grove" wrote:
> >>"sony654" <sony654@discussions.microsoft.com> wrote...
> >>>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)
>
> 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>.
>
>
>