formula problem

umzsexythang

Board Regular
Joined
Jul 11, 2003
Messages
50
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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! :LOL:
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
One set up...

Create the following Admin sheet:
umzsexythang.xls
ABCDE
1Last-2Last-1Last
2210
3ACTION304550
4CHECKLEVELS304550
5NOACTION506065
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
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
Joined
Jul 11, 2003
Messages
50

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jul 11, 2003
Messages
50

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Jul 11, 2003
Messages
50
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,888
Messages
5,766,948
Members
425,389
Latest member
Naresha

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top