• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Averaging Based on Multiple Criteria

Joe Shaer

New Member
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.

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

Attachments

  • Solution file.xlsx
    9.4 KB · Views: 6
The function AVERAGEIFS also exists and gives the same result.
Code:
= 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.
 

Attachments

  • Solution file.xlsx
    13.2 KB · Views: 2
Back
Top