Using cell references in formulae

[JA]

I am using a simple table to keep track of daily sales
for a dozen or so employees. There is a column for their
names, each day of the week and the week's total (8 col
total) and a row for each employee.

I want to be able to display in a seperate cell not only
the current highest and second highest sales amounts
(easily done with MAX and LARGE respectively), but I want
to be able to also display the associated name with each
amount.

One method that I was hoping to be able to use, if
possible, is something that allows me to use operators on
the cell references themselves, rather than the value
contained within.

For instance, given this table of data:

A B
1 John 1200
2 Ashley 1400
3 Mark 1800
4 James 1300
5 Brian 1500

Is there a formula I could use to make the value of a
seperate cell be equal to the value in column A,
immediately to the left of the highest value in column B.

Unfortunately, since this needs to be a file shared by
colleagues who are very new to Excel, using the R#C#
method of cell reference for the sheet is not an option.

Any suggestions?

 

[Biff]

Hi JA!

To return only the name associated with the max sales:

=INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0))

To return in descending order all the names:

=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5,ROW(A1)),B$1:B$5,0))

Neither formula accounts for ties.

Biff

 

[Guest]

Hello JA,

I believe I have most of the dilemma solved. Although, my guess is there may be an easier way. And, this may not be the way you wanted to way you wanted to formulate the results. The formulas are based on what you described as your columns and rows
A B C D E F G H
1 Name Mon. Tue. Wed. Thu. Fri. Sat. Totals
2 John 1200 1201 1202 1203 1204 1205 7215
3 Ashley 1400 1401 1402 1403 1404 1405 8415
4 Mark 1800 1801 1802 1803 1304 1805 10315
5 James 1300 1301 1302 1303 1804 1305 8315
6 Brian 1500 1501 1502 1503 1504 1505 9015

A9 - Highest Daily Sales:
A10 - 2nd Highest Daily Sales:
A12 - Highest Weekly Sales:
A13 - 2nd Highest Weekly Sales:

These formulas give you the highest and 2nd highest sales for both the daily and the weekly
B9 - "=LARGE($B$2:$G$6,1)"
B10 - "=LARGE($B$2:$G$6,2)"
B12 - "=LARGE($H$2:$H$6,1)"
B13 - "=LARGE($H$2:$H$6,2)"

These formulas give you the name of the person who had the highest and second highest daily and weekly sales.

C9 - "=IF(LARGE(B2:G2,1)=B9,A2,IF(LARGE(B3:G3,1)=B9,A3,IF(LARGE(B4:G4,1)=B9,A4,IF(LARGE(B5:G5,1)=B9,A5,IF(LARGE(B6:G6,1)=B9,A6)))))"
C10 - Still Working on this one
C12 - "=IF(LARGE(H2:H2,1)=B12,A2,IF(LARGE(H3:H3,1)=B12,A3,IF(LARGE(H4:H4,1)=B12,A4,IF(LARGE(H5:H5,1)=B12,A5,IF(LARGE(H6:H6,1)=B12,A6)))))"
C13 - "=IF(B13=H2,A2,IF(B13=H3,A3,IF(B13=H4,A4,IF(B13=H5,A5,IF(B13=H6,A6)))))"

 

[Domenic]

The following is based on your description of how your table is laid
out, that being, columns for Names, Days of the Week, and Week's Total
(Columns A through I):

J2, copied down:

=IF(A2<>"",RANK(I2,$I$2:$I$6)+COUNTIF($I$2:I2,I2)-1,"")

K1 enter 2 for the two highest sales (can be changed to the number of
your choosing)

L2, copied down:

=IF(ROW()-ROW(L$2)+1<=$K$1,INDEX(A:A,MATCH(ROW()-ROW(L$2)+1,$J:$J,0)),"")

M2, copied down:

=IF(ROW()-ROW(M$2)+1<=$K$1,INDEX(I:I,MATCH(ROW()-ROW(M$2)+1,$J:$J,0)),"")

This approach will take into account tied sales amounts. However, if
for example you want the top two returned, and there are two or more
tied for second, only the first occurrence will be returned.

Hope this helps!