Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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(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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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) |
#5
|
|||
|
|||
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) |
#6
|
|||
|
|||
"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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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" 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. |
#9
|
|||
|
|||
"sony654" 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) |
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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" wrote in message ... 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. |
#12
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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. |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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:P410)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5) Thanks - Tom ================================================== "Harlan Grove" wrote: "sony654" 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) |
#15
|
|||
|
|||
"sony654" 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:P410)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5) .... "Harlan Grove" wrote: "sony654" 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. |
#16
|
|||
|
|||
Harlan - Thankyou very much. Your advise is right on. Thans again - Tom
"Harlan Grove" wrote: "sony654" 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:P410)*ISNUMBER(B41:P41)),B41:P41)-(I5*J5) .... "Harlan Grove" wrote: "sony654" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Then statement help needed | Excel Worksheet Functions | |||
If Statement help needed please | Excel Worksheet Functions | |||
If statement help needed please | Excel Discussion (Misc queries) | |||
If statement help needed | Excel Worksheet Functions | |||
If statement needed | Excel Worksheet Functions |