Average values between peaks within a data range

[stephboucher]

I have a data range which contains multiple peaks - i want to sum the values after a peak, up to and including the next peak, then averaging the cells.

Please see attached data sheet

Thanks in advance for your help

regards

Steph

[mikeTRON]

I dont think this is the most elegant but it is the easiest to understand.
If you wanted to you could combine two of those row into ONE but unless you need the space I wouldnt bother, as this makes the calculations really easy to audit.

[MrShorty]

Here's how I might do it

1) add a helper row with something like =COUNT($B3:B3) (copy across). That should give you a series of descending numbers (6,5,4,...) where each number identifies a given peak. Note how I mixed relative and absolute references to get this count.
2) Now, a simple AVERAGEIF() function will compute the average for each peak. =AVERAGEIF($B$4:$M$4,5,$B$2:$M$2). Replace 5 with a suitable cell reference if you want to have the peak numbers in a cell.

[mikeTRON]

Here's how I might do it
1) add a helper row with something like =COUNT($B3:B3) (copy across). That should give you a series of descending numbers (6,5,4,...) where each number identifies a given peak. Note how I mixed relative and absolute references to get this count.
2) Now, a simple AVERAGEIF() function will compute the average for each peak. =AVERAGEIF($B$4:$M$4,5,$B$2:$M$2). Replace 5 with a suitable cell reference if you want to have the peak numbers in a cell.

That is basically what I did in the attachment.

[stephboucher]

thanks for your help on this - i'll try to combine into a single formula but this is a great help.