Average formula for a specific condition

[rrmacman]

I have been asked at work to get some averages of data from an excel sheet. I have attached a sample workbook of the data i will be using.

On Sheet1 is the data that will be looked at and on sheet2 is a sample of the results i need to achieve. I can get the data on sheet2 easy if i knew that data was static but in my case the data is going to be dynamic, so i don't know how many records each person is going to have each time if that person shows up in the record set at all. This rules out on static ranges to gather averages.

So far what i came up with is i use a macro to copy all the "Names" column to sheet 2 and then filter that data so that each name is unique, sort like a "key" for looking up values. Now i need to get averages of "Total Hours" worked in sheet 1 and display it in sheet 2 for each "Name". I have a formula worked out and almost have it where i want it but cant seem to get the range of data i need to be looking at.

I need to get the Average Of hours Worked Per Person Where "Wk End Date" is greater than 1/1/2008 and the "name" matches up with the name in sheet 2.

I have... ( this formula doesnt figure in the dates yet.. any help would be appreciated on that part as well.)

Please Login or Register  to view this content.

but this code looks at the whole column of F:F in sheet 1 but i want the range in Column F where Column B equals a "Name" in Column A in Sheet 2.


I figured if i can figure this formula out i can use it to get the Averages for the other information. I hope this isn't too confusing. If you need any other information let me know.

[martindwilson]

see atached put your last date and refer to that i used f1

[Palmetto]

Just another option. Could you use a Pivot Table?

If it is based on a dynamic named ranged it only needs to be refreshed when the data changes and this is easily automated with a bit of VBA code, plut is adds more flexibility to quickly get different views of the data.

See attached.

[rrmacman]

Both of these are really helpful, thank you very much. One thing though, the pivot table could work but might be very long as the "real" list has over 5000 distinct names. But i guess i can try it and give to my boss with both options and they can choose. Thanks again

Edit:

I guess a pivot table work as the names wont fit on the header row of the pivot table because it exceeds the limit