Averaging Based on Multiple Criteria

[Joe Shaer]

Hello -

I would like to create a quick averaging tool based on multiple criteria's.
Criteria #1: Route Number
Criteria #2: County Name
Criteria #3: Begin Mile Range
Criteria # 4: End Mile Range

My challenge is that if the ' begin and End mile range' go over multiple segments, how do I average all data points between?

For example (image below) - my begin mile is at '1.229' and the end mile is '5.238'. I would like to average everything between them.

 

[amit_gupta123]

Hi Joe

it would be great if you share an excel sheet as an attachment

Thanks

 

[pecoflyer]

A Pivot Table should do the job quite easily

 

[amit_gupta123]

Attaching the file basis image and getting the expected result. Hope that would help. Formula used sumifs & Countifs

 

[Joe Shaer]

Hi Joe

it would be great if you share an excel sheet as an attachment

Thanks

Attaching the file basis image and getting the expected result. Hope that would help. Formula used sumifs & Countifs

Awesome!!! Thank you so much! In the future I will definately use sample attachments. Again, thanks!

 

[Peter Bartholomew]

The function AVERAGEIFS also exists and gives the same result.

= AVERAGEIFS(Table1[Value],
     Table1[Route],  Route,
     Table1[County], County,
     Table1[Miles],  ">="&Begin,
     Table1[Miles],  "<="&End
  )

It looks somewhat different because I use defined names and structured references in place of directly referenced ranges.