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
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
Hello
you can use vlookup as well, or can you please attach a sample file for more details.
__________________
Regards
Rahul Nagar
Founder of www.myshortcutkeys.com.
If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
Hi,
Is this required in a single cell? If not you could use the following starting in column U, say U4 and copied across.
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.Please Login or Register to view this content.
Rgds
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
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!
So the logic is - "which column, when summed cumulatively brings the total over the inventory?"
right?
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.
How about this:
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
Last edited by Cheeky Charlie; 03-04-2009 at 12:36 PM.
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.
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
Last edited by Cheeky Charlie; 03-04-2009 at 12:39 PM.
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
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
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!
=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
You are a genius!! It worked! Yeah!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks