asked on

In Excel, how to find all values lying between 2 values and find average of the corresponding values in those rows?

Got a doubt in excel. I have wind speed and power values. I need to find the average of all power values for wind speeds lying between 2 values.
Perhaps this can be done with SUMIF and INDEX MATCH for 2 criteria?
Please find attached the excel sheet.
Any help is much appreciated.
Thanks!
wind.xlsx

Fabrice Lambert

afraid no file is attached.

Roy Cox

You could Format the Data as a Table, then filter the data to display the required wind speeds. A Table can have a Total row and you can choose that Row to show the average. The calculation will only use the filtered values.

This article explains how to set up a Table with a Total Row

Table Formula in Excel

ASKER CERTIFIED SOLUTION

abbas abdulla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Hi Eric

I've formatted the example as a Table, added a Total Row set to display Average.

I've filtered for values > 80 and <100 to produce an average.

This means you can be dynamic with your requirements
wind.xlsx

Eric Philips

ASKER

Hi Roy,
I wanted to let excel do the work of finding which interval the values lie in and then return average for all those values.

In the Table method, I'd have to manually do the filtering.
But thanks, now I know about Table calculations :)