Nested If's

[Beverly]

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 didn’t work. So I am trying to
used nested IF’s 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"),AND(E7>=2500,R7<555,M7="1"),AND(E7>=2473,R7>1650,M7="1"),AND(E7>=15000,R7>1650,M7="1"),AND(E7>=15000,R7>1649,M7="1")),"SUSPECT",""))),IF(OR(AND(E7>=2473,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"),AND(E7>=2473,R7>1650,M7="G"),AND(E7>=15000,R7>1650,M7="G"),AND(E7>=15000,R7>1649,M7="G")),"SUSPECT",""))

Sorry this is so involved. Thanks in advance for any suggestions.

Beverly

 

[Sheeloo]

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,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]

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