Formula for finding averages based on numerical data

**olsebk01** · 08-22-2017, 01:37 PM

Hello,

I am working with a spreadsheet that contains numerical data. To give some context, the data is measurements of depth, dissolved oxygen and temperature in a lake taken every 0.1 second. I want to compile average DO and temperature for every meter, but have multiple measurements within a meter. The N for each measurement within a meter is not constant.

Example:
A B C
DEPTH (m) DO (mg/l) TEMP (C)
1 0.672 11.46 11.19
2 0.673 11.46 11.19
3 0.673 11.46 11.19
4 1.589 11.47 10.9
5 1.612 11.48 10.89
6 1.685 11.48 10.85

I was trying to use an =AVERAGEIFS statement such as

=AVERAGEIFS(B:B, A:A, ">0" & <1") to get an average of 11.46 for >1m
and
=AVERAGEIFS(B:B, A:A, ">1" & <2") to get an average of 11.47 for an average within the 1m measurements

Same for temperature

Some of my lakes are 60 meters, so I wanted a quick way to get all of the averages for each meter, but my statements have only produced #DIV/0!

Any help on how to quickly and efficiently process these data without having to go through and highlight cells using the =AVERAGE function would be very helpful.

Thank you!

**Aardigspook** · 08-22-2017, 04:06 PM

Welcome to the forum.

You need to put the two criteria in separately:

Formula:

Please Login or Register  to view this content.

To save you putting the depths into the formulae manually, you could put the 0 & 1, 1 & 2, etc into other cells, then use something like this (with the lower (shallower) figure in col E and higher (deeper) in col F):

Formula:

Please Login or Register  to view this content.

Hope that does what you want.

Edit: you'll see I've put <= into the formulae, not just < so you get measurements for deeper than 0m down to 1m, deeper than 1m (i.e. from 1.00000000000001) down to 2m, etc.
If you prefer to have 0m to just under 1m (0.99999999999999m), 1m to just under 2m, etc, then use >= and < instead of > and <=. Hope that makes sense!