Use of CELL and/or OFFSET functions with MAX function

[PhilliesFan]

I am creating a spreadsheet in Excel 2002. The entire document will
consist of two sheets: the first sheet will calculate values based on
predetermined formulas, and the second sheet will contain data entered
for each month.

An example of data on Sheet 2 would look as follows:

Dec-03 Nov-03 Oct-03 Sep-03 Aug-03 Jul-03 Jun-03 May-03 .....
100 79 242 66 98 77 156 177

This data would be present for each of the 100+ customers listed on
this page.

On Sheet 1, I have created a formula to find the maximum value for the
previous 12 months of data on Sheet 2:

=MAX('Sheet 2'!$C$5:$N$5)

What I also need to retrieve is the month-year for the maximum value
for a particular range. So, in the example listed above, the maximum
value is 242; I need a formula which will recognize that 242 is the
maximum value, then look in the cell above that maximum value and
return the month-year in which the maximum value is reached (in the
sample above, the value would be "Oct-03").

I tried using a combination of the CELL function and the OFFSET
function to find this value:

=OFFSET(CELL("address",MAX('Sheet 2'!$C$5:$N$5)),-1,0)

This appears to me as if it should find the appropriate value: the
CELL function should find the address of the cell containing the
maximum value for the range, then the OFFSET function should look one
row above that address to retrieve the appropriate month-year.
However, whenever I try entering this into my spreadsheet, I only get
error messages, and none of the MS helpful hints seem to work.

Can someone let me know if what I am trying to do is possible, and if
so, how?

Thanks,

PhilliesFan

 

[Peo Sjoblom]

Try something like


=INDEX('Sheet 2'!$C$4:$N$4,MATCH(MAX('Sheet 2'!$C$5:$N$5),'Sheet
2'!$C$5:$N$5,0))

assuming the dates are in C4:N4

 

[Frank Kabel]

Hi
maybe the follwoing will help to get you the Month/Year value (assuming
values are in row 5; dates in row 4; starting in column C):
=INDEX('Sheet 2'!$C$4:$N$5,1,MATCH(MAX('Sheet 2'!$C$5:$N$5),'Sheet
2'!$C$5:$N$5,0))

Problems may arise, if two numbers are maximums
Frank

 

[Frank Kabel]

Hi Peo

i think, you missed the row reference in the INDEX function - or maybe
its just too late for me to understand your formula correctly

Frank

 

[Peo Sjoblom]

Hi Frank,

not really needed, obviously the orthodox way is

index(range,row#,column#)

but it doesn't matter in this case
but I try to be frugal and there are 2 characters less to type