you should be able to do this with an embedded IF statement

just want to check the validation table

anything <= 10days = level 9

>10 days and any workflow usd = will be level 8

>15 days and if workflows usd >10,000 = level 5

>30 days and if workflows usd anything under or = 10,000 = level 7

>30 days and if workflows usd >10,000 and <= 25,000 = level 3

>30 days and if workflows usd >25,000 and <= 50,000 = level 2

>30 days and if workflows usd >50,000 = level 1

>50 days is level 6 - but I assume you dont want to alter the USD - so thats actually

>50 days and if workflows usd <=10,000 = level 6

>70 days and if workflows usd <=10,000 = level 4

so I would almost write that IF statement

i'll try out and post back - but maybe tomorrow - so someone else may answer

would you confirm the above though

if so then I think this is the order the IF statement needs to be written to ensure the levels are not overwritten

>30 days and if workflows usd >50,000 = level 1

>30 days and if workflows usd >25,000 and <= 50,000 = level 2

>30 days and if workflows usd >10,000 and <= 25,000 = level 3

>70 days and if workflows usd <=10,000 = level 4

>50 days and if workflows usd <=10,000 = level 6

>30 days and if workflows usd <= 10,000 = level 7

>15 days and if workflows usd >10,000 = level 5

>10 days and any workflow usd = will be level 8

anything <= 10days = level 9

would you check the logic flow - and assume you start at the top and work down and that any changes in levels are correct

clearly the logic above is not the same as you have used in the spreadsheet

i used this formula and in the first few cells I got a level 5 - where you had a 1 or n/a

Code:

=IF(AND(H2>30,L2>50000),1,IF(AND(H2>30,L2>25000),2,IF(AND(H2>30,L2>10000),3,IF(H2>70,4,IF(H2>50,6,IF(H2>30,7,IF(AND(H2>15,L2>10000),5,IF(H2>10,8,9))))))))

so i have misunderstood the requirement completely

in rows 2, 3 and 4

M2 = level 1

you have H2 = 18 L2 = 686,647 - so not a level 1 as its less than 30days

but greater than 15 days and 10,000 = level 5

M3 = 1

you have H3= 18 L3 = 561500 - so not level 1 as its less than 30days

but greater than 15 days and 10,000 = level 5

M4 = N/A

you have H4 = 30 and L4 =304,311

so greater than 15days and greater than 10000 = level 5