|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 9th, 2010, 06:36 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus,
First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...help/7159.html) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xls Hopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet..  
|
|
#2
February 9th, 2010, 08:55 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
John -
If you want to keep everything immediate (that is, not run a macro), then you can split the contents into two other cells, and combine them into one that is visible. If your original code was in, say, cell AA7, you could use cells AB7 and AC7 to contain half the criteria each, and then AA7 would be simple like this: =IF(AB7 = "",AC7,AB7) Where AB7 might have =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/A",IF(N7="N/A","")) And AC7 would have the rest of the checking. -- Daryl S "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...help/7159.html) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xls Hopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. .
|
|
#3
February 9th, 2010, 09:06 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
I believe we can solve the multi-IF function by using a long OR function, and
we can bypass the N/A problem by using SUM (which ignores text). If I setup my sheet similar to what your's looks like, this formula appears to work: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)=30,SUM(-$W$2,L7)=30,AND(T7"A",SUM(-$W$2,M7)=30),SUM(-$W$2,N7)=30,SUM(-$W$2,O7)=30,SUM(-$W$2,P7)=30),"Yes","No"))) Since there are only 4 possible outcomes, the most IF functions you should need is 3. Hope that helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...help/7159.html) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xls Hopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. .
|
|
#5
February 10th, 2010, 04:56 AM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
You can simplify this further because you don't need the Sum function.
L7-$W$2 is the same as SUM(-$W$2,L7). Try the following: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(J7-$W$2=30,J7"N/A"),AND(L7-$W$2=30,L7"N/A"),AND(T7"A",M7-$W$2)=30,M7"N/A"),AND(N7-$W$2)=30,N7"N/A"),AND(O7-$W$2=30,O7"N/A"),AND(P7-$W$2=30,P7"N/A")),"Yes","No"))) Regards, Fred "retailmessiah" wrote in message ... Hi Daryl & Luke, Thanks for your replies. I tried to work with your formulas Daryl, but I was struggling to get the data to check out, and was trying to avoid extra columns, if possible. I then moved on to Luke's (obviously more condensed/better) formula, and it didn't exactly work either. Don't get me wrong, it mostly worked, but I was still having issues where it was incorrectly coding lines as expiring when I imported it back to our larger data set sheet. Then I realized that it was reporting a Yes on expiring in 30 days when it was encountering lines with a N/A in them. I reworked Luke's formula, and it appears to be working. Here's what I ended up with in the production sheet: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)=30,J7"N/ A"),AND(SUM(-$W$2,L7)=30,L7"N/A"),AND(T7"A",SUM(-$W $2,M7)=30,M7"N/A"),AND(SUM(-$W$2,N7)=30,N7"N/A"),AND(SUM(-$W $2,O7)=30,O7"N/A"),AND(SUM(-$W$2,P7)=30,P7"N/A")),"Yes","No"))) Just nested some AND checks for the N/A and it works perfectly. I realized after I saw Luke's formula that this was less of a formula issue I was having, and more of a logic issue... possibly/probably not one of my strong suits. It never occurred to me to work with the dates as integers using normal mathematical operation functions instead of date functions-- even though I was aware that you could, and had in the past! Very nice! Thank you gentlemen, and I did rate both posts on Google Groups star rating system, I hope that's what you were looking for. Thanks again for your assistance. -John Phenom On Feb 9, 3:06 pm, Luke M wrote: I believe we can solve the multi-IF function by using a long OR function, and we can bypass the N/A problem by using SUM (which ignores text). If I setup my sheet similar to what your's looks like, this formula appears to work: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)=30,SUM(-$W$2,L7)=30,AND(T7"A",SUM(-$W$2,M7)=30),SUM(-$W$2,N7)=30,SUM(-$W$2,O7)=30,SUM(-$W$2,P7)=30),"Yes","No"))) Since there are only 4 possible outcomes, the most IF functions you should need is 3. Hope that helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...ula-need-help/...) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xlsHopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. .
|
|
#6
February 10th, 2010, 06:26 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o V
Fred,
You can't do a direct subtraction because the N/A (or any text value) will cause a #VALUE error to pop-up. The error then propogates throughout the rest of the formula, causing an overall error. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Fred Smith" wrote: You can simplify this further because you don't need the Sum function. L7-$W$2 is the same as SUM(-$W$2,L7). Try the following: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(J7-$W$2=30,J7"N/A"),AND(L7-$W$2=30,L7"N/A"),AND(T7"A",M7-$W$2)=30,M7"N/A"),AND(N7-$W$2)=30,N7"N/A"),AND(O7-$W$2=30,O7"N/A"),AND(P7-$W$2=30,P7"N/A")),"Yes","No"))) Regards, Fred "retailmessiah" wrote in message ... Hi Daryl & Luke, Thanks for your replies. I tried to work with your formulas Daryl, but I was struggling to get the data to check out, and was trying to avoid extra columns, if possible. I then moved on to Luke's (obviously more condensed/better) formula, and it didn't exactly work either. Don't get me wrong, it mostly worked, but I was still having issues where it was incorrectly coding lines as expiring when I imported it back to our larger data set sheet. Then I realized that it was reporting a Yes on expiring in 30 days when it was encountering lines with a N/A in them. I reworked Luke's formula, and it appears to be working. Here's what I ended up with in the production sheet: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)=30,J7"N/ A"),AND(SUM(-$W$2,L7)=30,L7"N/A"),AND(T7"A",SUM(-$W $2,M7)=30,M7"N/A"),AND(SUM(-$W$2,N7)=30,N7"N/A"),AND(SUM(-$W $2,O7)=30,O7"N/A"),AND(SUM(-$W$2,P7)=30,P7"N/A")),"Yes","No"))) Just nested some AND checks for the N/A and it works perfectly. I realized after I saw Luke's formula that this was less of a formula issue I was having, and more of a logic issue... possibly/probably not one of my strong suits. It never occurred to me to work with the dates as integers using normal mathematical operation functions instead of date functions-- even though I was aware that you could, and had in the past! Very nice! Thank you gentlemen, and I did rate both posts on Google Groups star rating system, I hope that's what you were looking for. Thanks again for your assistance. -John Phenom On Feb 9, 3:06 pm, Luke M wrote: I believe we can solve the multi-IF function by using a long OR function, and we can bypass the N/A problem by using SUM (which ignores text). If I setup my sheet similar to what your's looks like, this formula appears to work: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)=30,SUM(-$W$2,L7)=30,AND(T7"A",SUM(-$W$2,M7)=30),SUM(-$W$2,N7)=30,SUM(-$W$2,O7)=30,SUM(-$W$2,P7)=30),"Yes","No"))) Since there are only 4 possible outcomes, the most IF functions you should need is 3. Hope that helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...ula-need-help/...) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xlsHopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. . .
|
|
#7
February 10th, 2010, 06:27 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o V
Glad you got it to work, and thanks for the feedback!
-- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hi Daryl & Luke, Thanks for your replies. I tried to work with your formulas Daryl, but I was struggling to get the data to check out, and was trying to avoid extra columns, if possible. I then moved on to Luke's (obviously more condensed/better) formula, and it didn't exactly work either. Don't get me wrong, it mostly worked, but I was still having issues where it was incorrectly coding lines as expiring when I imported it back to our larger data set sheet. Then I realized that it was reporting a Yes on expiring in 30 days when it was encountering lines with a N/A in them. I reworked Luke's formula, and it appears to be working. Here's what I ended up with in the production sheet: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)=30,J7"N/ A"),AND(SUM(-$W$2,L7)=30,L7"N/A"),AND(T7"A",SUM(-$W $2,M7)=30,M7"N/A"),AND(SUM(-$W$2,N7)=30,N7"N/A"),AND(SUM(-$W $2,O7)=30,O7"N/A"),AND(SUM(-$W$2,P7)=30,P7"N/A")),"Yes","No"))) Just nested some AND checks for the N/A and it works perfectly. I realized after I saw Luke's formula that this was less of a formula issue I was having, and more of a logic issue... possibly/probably not one of my strong suits. It never occurred to me to work with the dates as integers using normal mathematical operation functions instead of date functions-- even though I was aware that you could, and had in the past! Very nice! Thank you gentlemen, and I did rate both posts on Google Groups star rating system, I hope that's what you were looking for. Thanks again for your assistance. -John Phenom On Feb 9, 3:06 pm, Luke M wrote: I believe we can solve the multi-IF function by using a long OR function, and we can bypass the N/A problem by using SUM (which ignores text). If I setup my sheet similar to what your's looks like, this formula appears to work: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)=30,SUM(-$W$2,L7)=30,AND(T7"A",SUM(-$W$2,M7)=30),SUM(-$W$2,N7)=30,SUM(-$W$2,O7)=30,SUM(-$W$2,P7)=30),"Yes","No"))) Since there are only 4 possible outcomes, the most IF functions you should need is 3. Hope that helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...ula-need-help/...) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xlsHopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. . .
|
|
#8
February 10th, 2010, 06:52 PM
posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
|
Too Many IF Statements Nesting Error (Excel Formula Loop w/o V
Good point. Thanks for pointing it out.
Fred "Luke M" wrote in message ... Fred, You can't do a direct subtraction because the N/A (or any text value) will cause a #VALUE error to pop-up. The error then propogates throughout the rest of the formula, causing an overall error. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Fred Smith" wrote: You can simplify this further because you don't need the Sum function. L7-$W$2 is the same as SUM(-$W$2,L7). Try the following: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(J7-$W$2=30,J7"N/A"),AND(L7-$W$2=30,L7"N/A"),AND(T7"A",M7-$W$2)=30,M7"N/A"),AND(N7-$W$2)=30,N7"N/A"),AND(O7-$W$2=30,O7"N/A"),AND(P7-$W$2=30,P7"N/A")),"Yes","No"))) Regards, Fred "retailmessiah" wrote in message ... Hi Daryl & Luke, Thanks for your replies. I tried to work with your formulas Daryl, but I was struggling to get the data to check out, and was trying to avoid extra columns, if possible. I then moved on to Luke's (obviously more condensed/better) formula, and it didn't exactly work either. Don't get me wrong, it mostly worked, but I was still having issues where it was incorrectly coding lines as expiring when I imported it back to our larger data set sheet. Then I realized that it was reporting a Yes on expiring in 30 days when it was encountering lines with a N/A in them. I reworked Luke's formula, and it appears to be working. Here's what I ended up with in the production sheet: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)=30,J7"N/ A"),AND(SUM(-$W$2,L7)=30,L7"N/A"),AND(T7"A",SUM(-$W $2,M7)=30,M7"N/A"),AND(SUM(-$W$2,N7)=30,N7"N/A"),AND(SUM(-$W $2,O7)=30,O7"N/A"),AND(SUM(-$W$2,P7)=30,P7"N/A")),"Yes","No"))) Just nested some AND checks for the N/A and it works perfectly. I realized after I saw Luke's formula that this was less of a formula issue I was having, and more of a logic issue... possibly/probably not one of my strong suits. It never occurred to me to work with the dates as integers using normal mathematical operation functions instead of date functions-- even though I was aware that you could, and had in the past! Very nice! Thank you gentlemen, and I did rate both posts on Google Groups star rating system, I hope that's what you were looking for. Thanks again for your assistance. -John Phenom On Feb 9, 3:06 pm, Luke M wrote: I believe we can solve the multi-IF function by using a long OR function, and we can bypass the N/A problem by using SUM (which ignores text). If I setup my sheet similar to what your's looks like, this formula appears to work: =IF(COUNTA(B7,J7,L7:P7)7,"No Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)=30,SUM(-$W$2,L7)=30,AND(T7"A",SUM(-$W$2,M7)=30),SUM(-$W$2,N7)=30,SUM(-$W$2,O7)=30,SUM(-$W$2,P7)=30),"Yes","No"))) Since there are only 4 possible outcomes, the most IF functions you should need is 3. Hope that helps! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "retailmessiah" wrote: Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/ A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W $2,P7,"D")30,"Yes","No")),IF(DATEDIF($W $2,O7,"D")30,"Yes","No")),IF(DATEDIF($W $2,N7,"D")30,"Yes","No")),IF(DATEDIF($W $2,M7,"D")30,"Yes","No")),IF(DATEDIF($W $2,L7,"D")30,"Yes","No")),IF(DATEDIF($W$2,J7,"D") 30,"Yes","No")) This formula is in a column labeled as "Expiring in 30 Days?" and should have a result of Yes or No for each row depending on if ANY of the dates are inside of 30 days until expiration. Let me explain. In this formula, Cell W2 contains the current date. Cell B7 is a count cell, and should only be in the first part. Cells J7, L7, M7, N7, O7, and P7 all contain expiration dates. This sheet was working until I discovered that some of the data in these cells needed to also accept N/A as a value. With just calcing if the DATEDIF was greater than/less than 30 days we began getting VALUE errors. The previous working formula was as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""), "No Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7, "D")30,DATEDIF($W $2,L7,"D")30,DATEDIF($W$2,M7,"D")30,DATEDIF($W $2,N7,"D")30,DATEDIF($W$2,O7,"D")30,DATEDIF($W $2,P7,"D")30),"Yes","No"))) Basically I need the formula to ignore the cell if it has N/A, but not stop the formula from checking the other cells in the row. There is one more complicating factor. One cell, M7 we want to IGNORE expiration dates and return a 'No' for that cell IF cell T7 has an "A" in it. Confused? I was too! So, all the error checking that's happening in the above formula with the caveat that it need not flag M7 as expiring in 30 days if cell T7="A". Ugh. Any help would be appreciated. I've been stuck here for a few days, and I feel like I'm overlooking something simple. I'm thinking after reading on some forums (i.e. http://www.computing.net/answers/off...ula-need-help/...) that I'd need to have an array formula(e) ? I'm not sure how to work with several of the functions that were listed in the example, so hopefully someone here can help me adapt this over-nested formula. I'd really like to keep it in the cell, and not need to jump to VBA if at all possible. I've posted a copy of one of the sheets based on this he http://john.highoctane.net/Expiry.xlsHopefully being able to see the conditional formatting that is based on this will be helpful too. I've ran some mock numbers so you can play with the sheet and see how it works. You'll see that it's mostly working, with the exception of the formulae above located in column W. The specific issue is where we get the #VALUE error. Thanks for your assistance. -John Phenom (this is a valid email, just anticipating spam once it's out on Usenet.. . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
