Sam,

These formula look familiar, I think I did these a couple of days ago!!

Anyway, you want the largest 10 shops and companies:-

Largest 10 shops array enter (see below) and drag down 9 rows.
=INDEX($A$1:$E$1,MAX(($A$1:$E$5=LARGE($A$1:$E$5,RO W(A1)))*COLUMN($A$1:$E$5)-MIN(COLUMN($A$1:$E$5))+1))

Largest 10 Companies array enter and drag down 9 rows
=INDEX($F$2:$F$5,MAX(($A$2:$E$5=LARGE($A$2:$E$5,RO W(A1)))*ROW($A$2:$E$5)-MIN(ROW($A$2:$E$5))+1))

Having done these 2 I think you should be able to fathom out how to do the
MIN 10 but if you struggle then post back.

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"SAM" wrote:

I have the following set of data:

shop1 shop2 shop3 shop4 shop5
0.00015 0.23 0.09 0.1 0.69 vod
0.5 0.00005 0.999 0.26 0.95 tesco
0.93 1.56 0.94 2.5 0.32 c&a
0.0003 0.73 5.69 0.47 0.00001 dixons

I want to baisically form a summary page that shows the highest and lowest
values and then tells me what shop and what company the figure is from. I
have succesfully done this so far for the min and max values:

Company Shop
max value dixons shop3
min value dixons shop5

using the following formulas:

Max Value Company
=INDEX(F2:F5,MAX((A2:E5=MAX(A2:E5))*ROW(A2:E5)-MIN(ROW(A2:E5))+1))

Max Value Shop Number
=INDEX(A1:E1,MAX((A1:E5=MAX(A1:E5))*COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1))

Min Value Company
=INDEX(F2:F5,MIN(IF(A2:E5=MIN(A2:E5),ROW(A2:E5)-MIN(ROW(A2:E5))+1)))

Min Value Shop Number
=INDEX(A1:E1,MIN(IF(A1:E5=MIN(A1:E5),COLUMN(A1:E5)-MIN(COLUMN(A1:E5))+1)))

And these are working fine. Thing is i want the top five max values, and top
ten min values and their corresponding shop numbers....does anyone have any
idea how to do this? I tried using the B59=LARGE($B$59:$Y$82,10) and
B59<=SMALL($B$59:$Y$82,10) formulas but no luck at all.....would really
really appreciate someones thoughts.....thank you so much anyone - i know
this is a complicated query...


Sam