Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
Hello all -
I see that I have exceeded the limit of nested IF statements that Excel allows. I have read mulitudes of previous posts trying to the answer I need. I can't find anything for statements with an IF(AND) function however. I have read about VLOOKUPS and VBA codes, but can't figure out the right tweak to make them work. Here is the original formula that I wanted to work: =IF(AND(M19=G17,J14J17),J17,(IF(AND(M19=G18,J14J 18),J18,(IF(AND(M19=G19,J14J19),J19,(IF(AND(M19=G 20,J14J20),J20,(IF(AND(M19=G21,J14J21),J21,(IF(A ND(M19=G22,J14J22),J22,(IF(AND(M19=G23,J14J23),J 23,(IF(AND(M19=G24,J14J24),J24,(IF(AND(M19=G25,J1 4J25),J25,J14))))))))))))))))) Can anyone please assist in some VBA code or another way to get this formula to work? Thanks for your help! Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
If I understand your formula correctly, I think it can be reduced to:
=MIN(J14,OFFSET(J17,MATCH(M19,G17:G25,0)-1,0)) The MATCH() function replaces each of your (M19=G17, M19=G18, ...) conditions and then returns the value in column J for the match in column G and then uses its value only if it is less than J14... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
Very cool, thanks! Just for my knowledge...what does the MIN and
OFFSET functions do? Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
more than 7 nested IF(AND) statements in formula - VBA? **NEWBIE**
MIN() says to take the minimum value of all arguments.
OFFSET() says to pick up the data offset from the cell reference specified. For example, OFFSET(A1,2,1) says to get the value that is two rows below and 1 column to the right of cell A1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie needing help with nested VLOOKUP IF function.... | Excel Discussion (Misc queries) | |||
Nested lists in excel 2002 - newbie | Excel Discussion (Misc queries) | |||
Nested if statements | Excel Worksheet Functions | |||
Nested IF Statements | Excel Discussion (Misc queries) | |||
Help with Nested If Statements | Excel Discussion (Misc queries) |