formula problem

[umzsexythang]

Hi,

I need to do some anaylitical work on sales per week in a month for different items.

(Table 1)


A B C D E F
Wk1 Wk2 Wk3 Wk4 Response
1 Item X 24 45 34 0
2 Item Y 60 0 0 0
3 Item Z 40 55 0 0

The response column will have 3 possible values, ACTION, CHECK LEVELS, or NO ACTION, and these will depend on the sales values as per the table below

(Table 2)
Wk1 Wk2 Wk3
ACTION <30 <45 <50
CHECK LEVELS 30-50 45-60 50-65
NO ACTION >50 >60 >65

I want to produce a formula which incorporates the arguments in table 2, and works on the sales value for the most current week. So for Item X, the most current weeks sales is Wk3, for Item Y it is Wk1, and for item Z it is Wk 2. So, for example, in table 1, the results will be:


A B C D E F
Wk1 Wk2 Wk3 Wk4 Response
1 Item X 24 45 34 0 ACTION
2 Item Y 60 0 0 0 CHECK LEVELS
3 Item Z 40 65 0 0 NO ACTION

Right now, I would be putting in the following formual in the response column for Item X:

IF(E1<50,"ACTION",IF(E1<=65,"CHECK LEVELS",IF(E1>65,"NO ACTION")))

And for Item Y:

IF(B1<30,"ACTION",IF(B1<=50,"CHECK LEVELS",IF(B1>50,"NO ACTION")))

And for Item Z

IF(C3<45,"ACTION",IF(C3<=60,"CHECK LEVELS",IF(C3>60,"NO ACTION")))

This is fine, but I am dealing with about 2000 items, so it is very time consuming to put in each formula to ensure that the formula is picking up the cell which is the most current weeks sales, and then changing the arguments accrodingly.

Is there a way in which I can write a formula which will look at the sales values and automatically pick up the most current weeks sales, and then calculate the response using the current arguments from table 2?

Thanks....

 

[umzsexythang]

Excellent, that's perfect.

Thanks for you help