# How to exceed 8 max "if" statements

1. ## How to exceed 8 max "if" statements

I need to write an IF statement that exceeds the 8 statement maximum. Basically, what I want to say is: IF(U2>K2,1,IF(U2+V2>K2,2.........all the way up to U2...AM2>K2,19) How would I accomplish this? Thanks  Register To Reply

2. ## Re: How to exceed 8 max "if" statements

Hello

you can use vlookup as well, or can you please attach a sample file for more details.  Register To Reply

3. ## Re: How to exceed 8 max "if" statements

Hi,

Is this required in a single cell? If not you could use the following starting in column U, say U4 and copied across. ``Please Login or Register  to view this content.``
If you want this in a single cell can you upload an example workbook. There's probably a better way of achieving what you want if we can see the problem in context. Add a note or two explaining which cells you want to add and the results you expect.

Rgds  Register To Reply

4. ## Re: How to exceed 8 max "if" statements

Sorry about that, didn't realize you could upload a file... I have attached it. I know that there are DIV/O in some columns (I manually change these for internal purposes). I need the formula calculated in the one column called "CALC WOC" column K (highlighted in yellow). It tests the value of column K against each column starting with U (March-09) all the way to the end (Sep-10), to see how many months it would take to exhaust the inventory value in Cell K. So if Cell U (forecast) is lesser than Cell K (inventory) then it would return the result 1. The formula I have works, but it's limited because I can only have 8 nested If statements maximum, so I need to get around this. Thanks for your help!  Register To Reply

5. ## Re: How to exceed 8 max "if" statements

So the logic is - "which column, when summed cumulatively brings the total over the inventory?"

right?  Register To Reply

6. ## Re: How to exceed 8 max "if" statements

yes, each column represents MONTHS, so I want to know how many months (since each column represents 1 month) will it take to dissolve the inventory? Then I will take that entire formula and then write *4 to get the amount of weeks.  Register To Reply

7. ## Re: How to exceed 8 max "if" statements

M2=MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW(\$1:\$19))))

Entered with Ctrl+Shift+Enter
looks like this when done:
{=MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW(\$1:\$19))))}

HTH  Register To Reply

8. ## Re: How to exceed 8 max "if" statements

Hi, it doesn't work, it only returns the value of "0". Did it work in your test? I did end it with the CTRL+SHIFT+ENTER and the brackets appeared, but the formula returned a zero. Just by highlighting the forecast months (beginning at column U and going across, looking at the bottom of the screen count, I can manually see that 13 months will cover the inventory), therefore it should've returned a value of 13 for the first row.  Register To Reply

9. ## Re: How to exceed 8 max "if" statements

Yes, it does work in mine, although the original formula tests up to "the last month before the inventory is exceeded" (i.e. 12)
For 99% of cases, this would deliver "the first month when the inventory has been exceeded"

=MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW(\$1:\$19))))+1
(i.e. 12+1)

This returns 13 for me in xl03

CC

Working example attached  Register To Reply

10. ## Re: How to exceed 8 max "if" statements

Okay, that last change you made by adding the +1 worked... now I need to take that number and multiply it by 4 so I convert it into weeks. I tried just writing *4 at the end, but obviously that's wrong   Register To Reply

11. ## Re: How to exceed 8 max "if" statements

Not that wrong - it's really pretty logical:
=(MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW(\$1:\$19))))+1)*4
Ctrl+Shift+Enter

You need the brackets because you don't want no of months + (1*4) - you want (number of months + 1)*4...

HTH  Register To Reply

12. ## Re: How to exceed 8 max "if" statements

And the last (I promise part is this -- which might change the formula otherwise, I can manually enter the number "72":

I want the formula to calculate as you instructed above x 4 (to convert into weeks), however anything greater than 18 months would just give the answer 72. If not, again, I can simply manually type 72 in the columns that return an answer of greater than 72. Sorry for the last-minute changes and thanks for everything!   Register To Reply

13. ## Re: How to exceed 8 max "if" statements

=MIN((MATCH(K2,SUBTOTAL(9,OFFSET(U2,0,0,1,ROW(\$1:\$19))))+1)*4,72)
CSE
Sure you can see what this does.

Yes, it would have been much easier if you had explained all this to start with - I'm not grumpy but please do it that way round next time HTH  Register To Reply

14. ## Re: How to exceed 8 max "if" statements

You are a genius!! It worked! Yeah!!  Register To Reply