# formula problem

#### umzsexythang

##### Board Regular
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....

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### Brian from Maui

##### MrExcel MVP
To find the last non 0 numberic entry.

=INDEX(\$A1:\$X1,LARGE((1-ISBLANK(\$A1:\$X1))*COLUMN(\$A1:\$X1),2)-COLUMN(\$1)+1)

enter with CTRL+SHIFT+ENTER

If you don't have 0's

=LOOKUP(9.9999999999999E+307,A1:X1)

Or

=INDEX(A1:X1,MATCH(9.9999999999999E+307,A1:X1))

Edit,

If it works, credit to Aladin!

##### MrExcel MVP
One set up...

umzsexythang.xls
ABCDE
1Last-2Last-1Last
2210
3ACTION304550
4CHECKLEVELS304550
5NOACTION506065
6
7

Select colored range, go to the Name Box on the Formula Bar, type DecisionTable, and hit enter.

Sales (the data sheet)

Don't use 0 for "no data yet" regarding the last week of a given item.
umzsexythang.xls
ABCDEFGH
1Last3
2PosLastLastValResponseItemWk1Wk2Wk3Wk4
3334ACTIONX244534
4160NOACTIONY60
5255CHECKLEVELSZ4055
Sales

Formulas...

A3:

=MATCH(9.99999999999999E+307,3:3)-CELL("Col",E3)+1

B3:

=LOOKUP(9.99999999999999E+307,3:3)

C1:

=MAX(A:A)

C3:

=IF(B3< INDEX(DecisionTable,2,MATCH(\$C\$1-A3,INDEX(DecisionTable,1,0),-1)),"ACTION",IF(B3 > INDEX(DecisionTable,4,MATCH(\$C\$1-A3,INDEX(DecisionTable,1,0),-1)),"NO ACTION","CHECK LEVELS"))

#### umzsexythang

##### Board Regular
Thanks for that guys,

I will try it out in work tomorrow, so fingers crossed!

#### umzsexythang

##### Board Regular
Sorry guys, my excel knowledge is not very good, so although this looks like exactly what I need, I do not really understand the formulas:

=MATCH(9.99999999999999E+307,3:3)-CELL("Col",E3)+1

and

=LOOKUP(9.99999999999999E+307,3:3)

Could you explain to me in laymen terms what these are doing please!! It's just that I want to move columns A,B and C in the Sales datasheet to after the Wk 4 column, so that I get the following order

Item, Wk1, Wk2, Wk3, wk4, Pos Last, Last/Last Val, Response.

I just want to know what impact this will have on the formulas and how to rectify them

Thanks again...

##### MrExcel MVP
umzsexythang said:
Sorry guys, my excel knowledge is not very good, so although this looks like exactly what I need, I do not really understand the formulas:

=MATCH(9.99999999999999E+307,3:3)-CELL("Col",E3)+1

and

=LOOKUP(9.99999999999999E+307,3:3)

Could you explain to me in laymen terms what these are doing please!! It's just that I want to move columns A,B and C in the Sales datasheet to after the Wk 4 column, so that I get the following order

Item, Wk1, Wk2, Wk3, wk4, Pos Last, Last/Last Val, Response.

I just want to know what impact this will have on the formulas and how to rectify them

Thanks again...

Those formulas are put before the data on purpose. Is there any special reason you want them after the data?

#### umzsexythang

##### Board Regular
It's just that this isn't all of the data that wiil be in the spreadsheet, and i think it would look better if the "response" column was at the end.

##### MrExcel MVP
umzsexythang said:
It's just that this isn't all of the data that wiil be in the spreadsheet, and i think it would look better if the "response" column was at the end.

If your data grows to the right (that is, the range is not fixed), the posted set up is the right thing to do, cosmetics notwithstanding.

If the data range fixed, they can be modified to meet your wish without disturbing the performance characteristics of the formulas.

#### umzsexythang

##### Board Regular
Hi,

Basically, the spreadsheet will have the following columns:

SKU, Cat Number, Title, Artist, Label, Units Bought, Units Available

Then after this, I would like to have:

Wk1,Wk2,Wk3,Wk4,Pos Last,Last Val,Response

And then after this there will be a column called "Action to be taken"

There will only ever be 4 weeks data, so there will only ever be Wk1-Wk4.

Is it possible to have the above layout? And, if so, how will the formulas need to be changed?

Hope this makes sense

##### MrExcel MVP
umzsexythang said:
...There will only ever be 4 weeks data, so there will only ever be Wk1-Wk4.

Is it possible to have the above layout? ...

With a fixed range, no problem...
aaSalesAnalysis umzsexythang v2.xls
HIJKLMNO
1Last3
2ItemWk1Wk2Wk3Wk4PosLastLastValResponse
3X244534334ACTION
4Y60160NOACTION
5Z4055255CHECKLEVELS
6
Sales

Formulas...

M3:

=MATCH(9.99999999999999E+307,I3:L3)

N3:

=LOOKUP(9.99999999999999E+307,I3:L3)

O1:

=MAX(M:M)

or: =MAX(M3:M5)

O3:

=IF(N3< INDEX(DecisionTable,2,MATCH(\$O\$1-M3,INDEX(DecisionTable,1,0),-1)),"ACTION",IF(N3 > INDEX(DecisionTable,4,MATCH(\$O\$1-M3,INDEX(DecisionTable,1,0),-1)),"NO ACTION","CHECK LEVELS"))

Note. If you like more info on the Match and Lookup formulas using 9.99999999999999E+307, do a search on the big number on this site.

Replies
1
Views
400
Replies
3
Views
300
Replies
1
Views
231
Replies
4
Views
145
Replies
3
Views
769

1,186,368
Messages
5,957,455
Members
438,306
Latest member
Crystal_Blue

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back