|
solving nested IF statements
I have too many nested if Statements! i need help figuring out how to reduce
them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
Hi,
could you please provide an example of your data and what you want to achieve , it will help "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
This may? be one way
=IF(ISNA(MATCH(1,A6:C6,-1)),B2,INDEX(6:6,MATCH(1,A6:C6,-1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Derrick" wrote in message ... I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
solving nested IF statements
see below.
so, something like: G6 =if(A6=0, "A6", if(B6=0, "B6",... if(E6=0,"E6",FORMULA)...)) A6:C6 are variables i want to check. if empty, or =0, their cell name should appear. after checking, the final formula will be in the ValueIfFalse block of the last 'variable check' if statement. better example: a6 = 100 b6 = 20 c6 = 10 d6 = 0 e6 = 15 : Since d6 = 0, G6 = "D6" if a6 = 100 b6 = 20 c6 = afds d6 = 20 e6 = 15 : Since C6 is not a number, G6 = "C6" finally, if a6 = 100 b6 = 20 c6 = 10 d6 = 4 e6 = 15 : G6 will equal the correct number, using a nested formula. which also has lots of ifs. this is simplified, but hopefully more throrough than before. "Eduardo" wrote: Hi, could you please provide an example of your data and what you want to achieve , it will help "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
This will return address of cell that equals zero, or is blank, and if none
is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
Ah, throwing a number check into the mix as well? Adjusting my formula, it
becomes: =IF(SUMPRODUCT((A6:E6=0)+ISBLANK(A6:E6)+ISTEXT(A6: E6))=0,YourFormula,ADDRESS(6,MIN(IF(ISBLANK(A6:E6) +(A6:E6=0)+ISTEXT(A6:E6),COLUMN(A6:E6))),4)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: see below. so, something like: G6 =if(A6=0, "A6", if(B6=0, "B6",... if(E6=0,"E6",FORMULA)...)) A6:C6 are variables i want to check. if empty, or =0, their cell name should appear. after checking, the final formula will be in the ValueIfFalse block of the last 'variable check' if statement. better example: a6 = 100 b6 = 20 c6 = 10 d6 = 0 e6 = 15 : Since d6 = 0, G6 = "D6" if a6 = 100 b6 = 20 c6 = afds d6 = 20 e6 = 15 : Since C6 is not a number, G6 = "C6" finally, if a6 = 100 b6 = 20 c6 = 10 d6 = 4 e6 = 15 : G6 will equal the correct number, using a nested formula. which also has lots of ifs. this is simplified, but hopefully more throrough than before. "Eduardo" wrote: Hi, could you please provide an example of your data and what you want to achieve , it will help "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
help me out. something is off.. and giving me a different cell name.
Ive changed it slightly to match my cells ----------- =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg") ----------- right now i have numbers <0 in all, except N6 = 0. it returns "H6" close....but no cigar can you explain how it works? IF(this, then, else) MATCH(lookup_value,lookup_array,match_type) INDEX(array,row_num,column_num) ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 1. why the ...,index(G6:N6=0,0),... the =0,0 part. 2. why the ...,0),0),4),"sdfg") the ,4 part thanks for your help! "NBVC" wrote: Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
solving nested IF statements
and by committing the formula you mean when im done writing it all and want
to move on to the next cell? i ahve to do that every time i change the formula right? also, what happens if i dont? "Luke M" wrote: This will return address of cell that equals zero, or is blank, and if none is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
Derrick;405046 Wrote: help me out. something is off.. and giving me a different cell name. Ive changed it slightly to match my cells ----------- =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg") ----------- right now i have numbers <0 in all, except N6 = 0. it returns "H6" close....but no cigar can you explain how it works? IF(this, then, else) MATCH(lookup_value,lookup_array,match_type) INDEX(array,row_num,column_num) ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 1. why the ...,index(G6:N6=0,0),... the =0,0 part. 2. why the ...,0),0),4),"sdfg") the ,4 part thanks for your help! "NBVC" wrote: Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html) View this thread: 'solving nested IF statements - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=112912) Since you are not starting at column A, you will need to offset the formula to look starting at column G =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0)+COLUMN(G6)-1,4),"sdfg") -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
solving nested IF statements
Luke. thanks!!
One more thing, basically just a modification - lets say there is some space inbetween the cells - ie not an array so: cells a6,b6,c6, ,e6. and d6 has words. can i change this formula to match? "Luke M" wrote: This will return address of cell that equals zero, or is blank, and if none is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
Correct. You should only have to input the formula once, why would you be
changing it often? If you don't input it as an array, it would cause the second IF function to return erroneous results, since it would be eviluating a single variable, as opposed to an entire array. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: and by committing the formula you mean when im done writing it all and want to move on to the next cell? i ahve to do that every time i change the formula right? also, what happens if i dont? "Luke M" wrote: This will return address of cell that equals zero, or is blank, and if none is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
See my response following Eduardo's comment. I modified my formula to check
for text as well, following your example's structure. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: Luke. thanks!! One more thing, basically just a modification - lets say there is some space inbetween the cells - ie not an array so: cells a6,b6,c6, ,e6. and d6 has words. can i change this formula to match? "Luke M" wrote: This will return address of cell that equals zero, or is blank, and if none is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
? sorry, your message isnt displayed.
"NBVC" wrote: Derrick;405046 Wrote: help me out. something is off.. and giving me a different cell name. Ive changed it slightly to match my cells ----------- =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg") ----------- right now i have numbers <0 in all, except N6 = 0. it returns "H6" close....but no cigar can you explain how it works? IF(this, then, else) MATCH(lookup_value,lookup_array,match_type) INDEX(array,row_num,column_num) ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 1. why the ...,index(G6:N6=0,0),... the =0,0 part. 2. why the ...,0),0),4),"sdfg") the ,4 part thanks for your help! "NBVC" wrote: Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html) View this thread: 'solving nested IF statements - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=112912) Since you are not starting at column A, you will need to offset the formula to look starting at column G =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0)+COLUMN(G6)-1,4),"sdfg") -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
solving nested IF statements
ok. Changing it only during the process of figuring out the formula.. once
im done im done changing it. "Luke M" wrote: Correct. You should only have to input the formula once, why would you be changing it often? If you don't input it as an array, it would cause the second IF function to return erroneous results, since it would be eviluating a single variable, as opposed to an entire array. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: and by committing the formula you mean when im done writing it all and want to move on to the next cell? i ahve to do that every time i change the formula right? also, what happens if i dont? "Luke M" wrote: This will return address of cell that equals zero, or is blank, and if none is found, will perform your formula. Adjust range sizes as needed. =IF(SUMPRODUCT(--(A6:E6=0)+ISBLANK(A6:E6))=0,YourFormula,ADDRESS(6, MIN(IF(ISBLANK(A6:E6)+(A6:E6=0),COLUMN(A6:E6))),4) ) It looks bulky, but there's only 1 nested function before your main function. Also, this is an array function, so you'll need to commit it using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Derrick" wrote: I have too many nested if Statements! i need help figuring out how to reduce them: my problem: to ensure the validity of a calculated formula, i am checking to see if a cell is blank or = 0 before imputting the formula, and then the formula has a lot of nested ifs as it is. so, something like: if(A6=0, "A6", if(B6=0, "B6", if(C6=0,"C6",FORMULA))) but much larger. anyone help? - make it simplier, more efficient? |
solving nested IF statements
nevermind i see it at the bottom.
ok . so this works for an array, or a column that is continuous. if i have cells inbetween, (see my 2nd response to Luke, and luke's response to Eduardo for better details) where i dont want to check for numbers, but texts, and so its not as prettily organized, can we modify this formula? so far im leaning towards using this formula, as its working the best so far. hahah sorry luke. time to step up ur game :P im a bit confused to the syntax still, but i think i can work around it. "NBVC" wrote: Derrick;405046 Wrote: help me out. something is off.. and giving me a different cell name. Ive changed it slightly to match my cells ----------- =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg") ----------- right now i have numbers <0 in all, except N6 = 0. it returns "H6" close....but no cigar can you explain how it works? IF(this, then, else) MATCH(lookup_value,lookup_array,match_type) INDEX(array,row_num,column_num) ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 1. why the ...,index(G6:N6=0,0),... the =0,0 part. 2. why the ...,0),0),4),"sdfg") the ,4 part thanks for your help! "NBVC" wrote: Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html) View this thread: 'solving nested IF statements - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=112912) Since you are not starting at column A, you will need to offset the formula to look starting at column G =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0)+COLUMN(G6)-1,4),"sdfg") -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
solving nested IF statements
Derrick;405129 Wrote: nevermind i see it at the bottom. ok . so this works for an array, or a column that is continuous. if i have cells inbetween, (see my 2nd response to Luke, and luke's response to Eduardo for better details) where i dont want to check for numbers, but texts, and so its not as prettily organized, can we modify this formula? so far im leaning towards using this formula, as its working the best so far. hahah sorry luke. time to step up ur game :P im a bit confused to the syntax still, but i think i can work around it. "NBVC" wrote: Derrick;405046 Wrote: help me out. something is off.. and giving me a different cell name. Ive changed it slightly to match my cells ----------- =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0),4),"sdfg") ----------- right now i have numbers <0 in all, except N6 = 0. it returns "H6" close....but no cigar can you explain how it works? IF(this, then, else) MATCH(lookup_value,lookup_array,match_type) INDEX(array,row_num,column_num) ADDRESS(row_num,column_num,abs_num,a1,sheet_text) 1. why the ...,index(G6:N6=0,0),... the =0,0 part. 2. why the ...,0),0),4),"sdfg") the ,4 part thanks for your help! "NBVC" wrote: Possibly: Code: -------------------- =IF(ISNUMBER(MATCH(TRUE,INDEX(A6:C6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(A6:C6=0,0),0),4),FORMULA) -------------------- adjust range A6:C6 to suit. -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' ('The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/members/nbvc.html)) View this thread: 'solving nested IF statements - The Code Cage Forums' ('solving nested IF statements - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh....php?t=112912)) Since you are not starting at column A, you will need to offset the formula to look starting at column G =IF(ISNUMBER(MATCH(TRUE,INDEX(G6:N6=0,0),0)),ADDRE SS(6,MATCH(TRUE,INDEX(G6:N6=0,0),0)+COLUMN(G6)-1,4),"sdfg") -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC' (http://www.thecodecage.com/forumz/member.php?userid=74) View this thread: 'solving nested IF statements - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=112912) Try then: Code: -------------------- =IF(ISNUMBER(MATCH(1,INDEX((G6:N6=0)+ISTEXT(G6:N6) ,0),0)),ADDRESS(6,MATCH(1,INDEX((G6:N6=0)+ISTEXT(G 6:N6),0),0)+COLUMN(G6)-1,4),"sdfg") -------------------- -- NBVC Where there is a will there are many ways. 'The Code Cage' (http;//www.thecodecage.com) ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112912 |
| All times are GMT +1. The time now is 07:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com