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....

 

[Brian from Maui]

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!

 

[Aladin Akyurek]

One set up...

Create the following Admin sheet:

umzsexythang.xls
	A	B	C	D	E
1		Last-2	Last-1	Last
2		2	1	0
3	ACTION	30	45	50
4	CHECKLEVELS	30	45	50
5	NOACTION	50	60	65
6
7
Admin

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
	A	B	C	D	E	F	G	H
1		Last	3
2	PosLast	LastVal	Response	Item	Wk1	Wk2	Wk3	Wk4
3	3	34	ACTION	X	24	45	34
4	1	60	NOACTION	Y	60
5	2	55	CHECKLEVELS	Z	40	55
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]

Thanks for that guys,

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

 

[umzsexythang]

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...

 

[Aladin Akyurek]

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]

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.

 

[Aladin Akyurek]

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]

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

 

[Aladin Akyurek]

...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
	H	I	J	K	L	M	N	O
1							Last	3
2	Item	Wk1	Wk2	Wk3	Wk4	PosLast	LastVal	Response
3	X	24	45	34		3	34	ACTION
4	Y	60				1	60	NOACTION
5	Z	40	55			2	55	CHECKLEVELS
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.