nick asked in Computers & InternetSoftware · 1 month ago

How to calculate a Average in Excel given a specific Value.?

Looking for any assistance. 

I am attempting to create a spread sheet to show average length of time for each function at my job. I would like to create individual average's and sums for each function, meaning if it shows "TRAFFIC STOP" in B2 then it takes the number from column E and Averages/Sums it but does not include the others such as ASSIST OTHER or ASSIST OWI. 

I have figured out how to get them to SUM and AVERAGE if I "sort" them but would like for excel to pull that data without me needing to sort every time. Meaning I would like to be able to see the SUM and AVERAGE for each individual category displayed in a text box on the side. I know this would be a longer forumla but it would save a ton of time going forward. 

Thanks for any help! 

Attachment image

1 Answer

Relevance
  • 1 month ago

    I would not deal with formulas like this.  Use a pivot table.  Select Total Hours as your data and set it to summarize by average.  Put your category as your Row.  That's it.

    You can add the Total Hours in again and show Sum if you like as well.

    If you don't know about Pivot tables, do a 5 minute video training.  It's easy to learn and much quicker than what you're trying to do.  Much more scalable as well.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.