Help!!!!!!!!!!!!

[rawky1976]

I have a spreadsheet with the total weekly sales values for five shops, I need to calculate the highest ranking shop for a particular week which I can easily do with say =MAX(C15:C20) but I dont want to display the figure. I need to display the branch name of the highest taking shop.
ie: -
if MAX c15:c20=c15 then display Acton
if MAX c15:c20=c16 then display Bracton
etc
?????????????????????????

Thank You

[sen_edp]

hello

take a look , i think it helps

http://www.mrexcel.com/board/viewtop...c=8355&forum=2

[Aladin Akyurek]

Hope Andreas is referring to a contrib of mine in

http://www.mrexcel.com/board/viewtop...c=3146&forum=2

Adapting that to your situation, I'd suggest to insert a worksheet which you can name Admin. That's where you can keep & compute things you don't want to show to the users.

Activate Admin.

In A2 enter: Max sales [ just a label ]

In B2 enter: =MAX(Sheet1!C15:C20)

In A3 enter: # Shops [ just a label ]

In B3 enter: =ROWS(Sheet1!C15:C20)

Select B2, go to the Name Box on the Formula Bar, type MaxSales.
Using the foregoing proc, name B3 NumShops.

Admin should look like this:

Microsoft Excel - aaRetHighestRankingShop Rawky1976.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A14		=
*	A	B	C	D
1	*	*	*	*
2	Max sales	:alert('=MAX(Sheet1!C15:C20)')>40	*	*
3	# Shops	:alert('=COUNTA(Sheet1!B15:B20)')>6	*	*
4	*	*	*	*
Admin

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


In the sheet where you have your data in C15:C20,

in E15 enter:

=INDEX(OFFSET($B$15,0,0,NumShops,1),MATCH(MaxSales,OFFSET($C$15,0,0,NumShops,1),0))

in E2 enter and copy down to a few rows:

=IF(COUNTIF(OFFSET($C$15,0,0,NumShops,1),MaxSales)>COUNTA($E$15:E15),INDEX(OFFSET($B$15,MATCH(E15,OFFSET($B$15,0,0,NumShops,1),0),0,NumShops,1),MATCH(MaxSales,OFFSET($C$15 ,MATCH(E15,OFFSET($B$15,0,0,NumShops,1),0),0,NumShops,1),0)),"")

The figure below shows why all this is needed.

Microsoft Excel - aaRetHighestRankingShop Rawky1976.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
I20		=
*	A	B	C	D	E
14	*	Shop	Sales	*	Highest Rank Shop(s)
15	*	Acton	20	*	:alert('=INDEX(OFFSET($B$15,0,0,NumShops,1),MATCH(MaxSales,OFFSET($C$15,0,0,NumShops,1),0))')>Tranton
16	*	Bracton	30	*	:alert('=IF(COUNTIF(OFFSET($C$15,0,0,NumShops,1),MaxSales)%3ECOUNTA($E$15:E15),INDEX(OFFSET($B$15,MATCH(E 15,OFFSET($B$15,0,0,NumShops,1),0),0,NumShops,1),MATCH(MaxSales,OFFSET($C$15,MATCH(E15,OFFSET($B$15,0,0,NumShops,1),0),0 ,NumShops,1),0)),"")')>Drenton
17	*	Tranton	40	*	:alert('=IF(COUNTIF(OFFSET($C$15,0,0,NumShops,1),MaxSales)%3ECOUNTA($E$15:E16),INDEX(OFFSET($B$15,MATCH(E 16,OFFSET($B$15,0,0,NumShops,1),0),0,NumShops,1),MATCH(MaxSales,OFFSET($C$15,MATCH(E16,OFFSET($B$15,0,0,NumShops,1),0),0 ,NumShops,1),0)),"")')>*
18	*	Drenton	40	*	:alert('=IF(COUNTIF(OFFSET($C$15,0,0,NumShops,1),MaxSales)%3ECOUNTA($E$15:E17),INDEX(OFFSET($B$15,MATCH(E 17,OFFSET($B$15,0,0,NumShops,1),0),0,NumShops,1),MATCH(MaxSales,OFFSET($C$15,MATCH(E17,OFFSET($B$15,0,0,NumShops,1),0),0 ,NumShops,1),0)),"")')>*
19	*	Qwerton	5	*	*
20	*	Yerton	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin

[sen_edp]

Hello all !

Hello Aladin , Yes you are correct, I had in mind your post for this.
You are doing great with excel formulas ,
I use your posts all the time to get answers - Thanks !

[Yogi Anand]

On 2002-05-18 13:52, rawky1976 wrote:
I have a spreadsheet with the total weekly sales values for five shops, I need to calculate the highest ranking shop for a particular week which I can easily do with say =MAX(C15:C20) but I dont want to display the figure. I need to display the branch name of the highest taking shop.
ie: -
if MAX c15:c20=c15 then display Acton
if MAX c15:c20=c16 then display Bracton
etc
?????????????????????????

Thank You

Hi rawky1976:
Here is another approach. Suppose your data for weekly sales is in cells B15:C20 as shown below

Acton 5
Bracton 7
other1 3
other2 4
other3 2
other4 1

then use the foloowing formula to get the name of the store with the maximum sale for the week:

=INDEX(B15:C20,MATCH(MAX(C15:C20),C15:C20,0),1)

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!




_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature


[ This Message was edited by: Yogi Anand on 2003-01-19 16:21 ]

[Aladin Akyurek]

Here is another approach. Suppose your data for weekly sales is in cells B15:C20 as shown below

Acton 5
Bracton 7
other1 3
other2 4
other3 2
other4 1

then use the foloowing formula to get the name of the store with the maximum sale for the week:

=INDEX(B15:C20,MATCH(MAX(C15:C20),C15:C20,0),1)

Another approach?

I wonder.