Calculating a peak value in a range of cells
 

Hi,
I'm trying to work out if this is possible to do in one cell.

I have a spreadsheet with time based data. Column A has the time values in
15min intervals, Column B has the data for that 15min period.

What I want at the bottom of Column B, is a value representing the HOUR
where there is the highest number of values in Column B - ie the range of 4
cells within the column that has the highest value.

At the moment, this is calculated by Column C summing a 4-cell range in
Column B. At the bottom of Column C, the "max" function is being used to
return the highest value.

Is it possible to do this in the one cell?

Calculating a peak value in a range of cells
 

It can be done but it's really complicated (if I do say so myself!).

It would have helped if you had included details as to the exact location of
the data.

Assume:

A1:A12 = time values
B1:B12 = numeric values

Array entered** :

=INDEX(A1:A12,MATCH(MAX(SUBTOTAL(9,OFFSET(B1,(ROW( INDIRECT("1:"&COUNT(B1:B12)/4))-1)*4,,4))),SUBTOTAL(9,OFFSET(B1,(ROW(INDIRECT("1:" &COUNT(B1:B12)/4))-1)*4,,4)),0)*4)

This is based on there being 4 readings for each interval so that the total
number of readings will be a multiple of 4. The formula will return the last
time value in the particular interval.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Joneru" wrote in message
...
Hi,
I'm trying to work out if this is possible to do in one cell.

I have a spreadsheet with time based data. Column A has the time values in
15min intervals, Column B has the data for that 15min period.

What I want at the bottom of Column B, is a value representing the HOUR
where there is the highest number of values in Column B - ie the range of
4
cells within the column that has the highest value.

At the moment, this is calculated by Column C summing a 4-cell range in
Column B. At the bottom of Column C, the "max" function is being used to
return the highest value.

Is it possible to do this in the one cell?