Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If's
I need to make a function that looks at an amount value (in Col E),
determines which account code is falls under (in Column M) and based on the account code determines if that value is greater than the following table based on number of days which is found in column R of the same record. Abbreviated example of a record: Col E: Col M: Col R: Item Amount Account Type Days Ex: 80000 G 15 Below is part of the table: Account Days MIN AMT Type 1 14 2,473.00 1 30 2,473.00 1 150 2,500.00 1 360 2,473.00 1 1095 15,000.00 1 9998 15,000.00 G 14 75,000.00 G 30 100,000.00 G 150 100,000.00 G 360 250,000.00 G 1095 250,000.00 G 9998 250,000.00 B 14 75,000.00 B 30 100,000.00 B 150 100,000.00 B 360 250,000.00 B 1095 250,000.00 B 9998 250,000.00 C 14 750.00 C 30 2,473.00 C 150 4,000.00 C 360 35,000.00 C 1095 250,000.00 C 9998 250,000.00 D 14 2,473.00 D 30 2,473.00 D 150 2,500.00 D 360 2,473.00 D 1095 15,000.00 D 9998 15,000.00 So I am trying to see if the Amount and number of Days in each record according to Account Type is greater than the amount in the table based on the table amount, day range, and account type. If it falls in those criteria, the cell is labeled Suspect. I tried to make a lookup function, but that didnt work. So I am trying to used nested IFs which contain the criteria for each account-day combination. However, in the example below I have created a function for account type 1 and account type G only. I get the result #Value instead of Suspect or . I eventually want to expand this function to include all the account types. Is this the best approach or is there a better way? If this is the best approach, what is my error in the formula: =IF(AND(OR(IF(OR(AND(E7=2473,R7<15,M7="1"),AND(E7 =2473,R7<45,M7="1"),AND(E7=2473,R7<195,M7="1"),A ND(E7=2500,R7<555,M7="1"),AND(E7=2473,R71650,M7 ="1"),AND(E7=15000,R71650,M7="1"),AND(E7=15000, R71649,M7="1")),"SUSPECT",""))),IF(OR(AND(E7=247 3,R7<15,M7="G"),AND(E7=2473,R7<45,M7="G"),AND(E7 =2473,R7<195,M7="G"),AND(E7=2500,R7<555,M7="G"),A ND(E7=2473,R71650,M7="G"),AND(E7=15000,R71650, M7="G"),AND(E7=15000,R71649,M7="G")),"SUSPECT"," ")) Sorry this is so involved. Thanks in advance for any suggestions. Beverly |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If's
You are complicating the problem...
Assuming you have your table in Col A, B and C of Sheet2 with no header row Insert a column before Col C and enter in the new Col C =A1&B1 and copy down till the end of your table Now in S1 9or row 1 of the Col you want) of sheet1 enter =IF((E1 VLOOKUP(M1&R1,Sheet2!C:D,2,FALSE)),"Suspect","Ok") and copy down This assumes that VLOOKUP WILL find a value in the table corresponding to the values in M and R... "Beverly" wrote: I need to make a function that looks at an amount value (in Col E), determines which account code is falls under (in Column M) and based on the account code determines if that value is greater than the following table based on number of days which is found in column R of the same record. Abbreviated example of a record: Col E: Col M: Col R: Item Amount Account Type Days Ex: 80000 G 15 Below is part of the table: Account Days MIN AMT Type 1 14 2,473.00 1 30 2,473.00 1 150 2,500.00 1 360 2,473.00 1 1095 15,000.00 1 9998 15,000.00 G 14 75,000.00 G 30 100,000.00 G 150 100,000.00 G 360 250,000.00 G 1095 250,000.00 G 9998 250,000.00 B 14 75,000.00 B 30 100,000.00 B 150 100,000.00 B 360 250,000.00 B 1095 250,000.00 B 9998 250,000.00 C 14 750.00 C 30 2,473.00 C 150 4,000.00 C 360 35,000.00 C 1095 250,000.00 C 9998 250,000.00 D 14 2,473.00 D 30 2,473.00 D 150 2,500.00 D 360 2,473.00 D 1095 15,000.00 D 9998 15,000.00 So I am trying to see if the Amount and number of Days in each record according to Account Type is greater than the amount in the table based on the table amount, day range, and account type. If it falls in those criteria, the cell is labeled Suspect. I tried to make a lookup function, but that didnt work. So I am trying to used nested IFs which contain the criteria for each account-day combination. However, in the example below I have created a function for account type 1 and account type G only. I get the result #Value instead of Suspect or . I eventually want to expand this function to include all the account types. Is this the best approach or is there a better way? If this is the best approach, what is my error in the formula: =IF(AND(OR(IF(OR(AND(E7=2473,R7<15,M7="1"),AND(E7 =2473,R7<45,M7="1"),AND(E7=2473,R7<195,M7="1"),A ND(E7=2500,R7<555,M7="1"),AND(E7=2473,R71650,M7 ="1"),AND(E7=15000,R71650,M7="1"),AND(E7=15000, R71649,M7="1")),"SUSPECT",""))),IF(OR(AND(E7=247 3,R7<15,M7="G"),AND(E7=2473,R7<45,M7="G"),AND(E7 =2473,R7<195,M7="G"),AND(E7=2500,R7<555,M7="G"),A ND(E7=2473,R71650,M7="G"),AND(E7=15000,R71650, M7="G"),AND(E7=15000,R71649,M7="G")),"SUSPECT"," ")) Sorry this is so involved. Thanks in advance for any suggestions. Beverly |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If's
Thank you for your reply. I had already tried that, but the problem is that
the "days" do not necessarily match those on the table. Where the table says 14, it means 14 days or less; 30 days or less, etc. My data table will show a number, say 10 which would be in the 14 range. But you gave me an idea. I inserted another column and entered this formula: =IF(R4<15,"14",IF(R4<31,"30",IF(R4<151,"150",IF(R4 <361,"360",IF(R4<1096,"1096","9998"))))). I will then use that value to concantenate to the account code. So thanks! -Beverly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested if's and lookup | Excel Worksheet Functions | |||
A problem with nested IF's | Excel Worksheet Functions | |||
A problem with nested IF's | Excel Worksheet Functions | |||
Nested If's | Excel Worksheet Functions | |||
How many nested IF's??? | Excel Discussion (Misc queries) |