Calculate the average number from the select profile

[jxh087]

Hi Everyone,

I have a huge lake dataset , and I need to calculate the average count from the 0-2 meters. an simple example goes like below:

[TABLE="width: 225"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Lake ID[/TD]
[TD]Depth (meter)[/TD]
[TD] Counts[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.2[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3.2[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.3[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone know if possible to do it in excel?

Many thanks! <audio controls="controls" style="display: none;"></audio>

 

[Joe4]

Welcome to the Board!

Let's say that your example you posted starts in cell A1 and goes to cell C9.
Then this formula will work:

=SUMIFS(C2:C9,B2:B9,">=0",B2:B9,"<=2")/COUNTIFS(B2:B9,">=0",B2:B9,"<=2")

 

[jxh087]

Thanks so much Joe, I really appreciated!

Only one thing I didn't mention clearly. I need the average count of each lake below two meters, in this case, will be the average of lake 22, 23 and 24. How should I include this information into the formula?


<audio controls="controls" style="display: none;"></audio>

 

[Joe4]

With both the SUMIFS and COUNTIFS function, you can add multiple conditions (which we did for the depth). So you can simply add another condition to each for each lake.

Give it a try and see how you do (it will be a good introduction into these functions and make sure you understand how they work).
Here are some links that give detailed explanations:
https://www.techonthenet.com/excel/formulas/sumifs.php
https://www.techonthenet.com/excel/formulas/countifs.php

Post back to this thread if you run into trouble, and show us what you tried, and we can help you clean it up.