calculate average - range depends on another field

[MoniNalu]

Hello everyone,

I have to calculate averages and the range changes. For example, John (workshop facilitator) had 6 attendees so he has six rows, one for each attendee; Jennifer (workshop facilitator) had two attendees so she has two rows, one for each attendee.
To add to the complexity, there are six columns of scores for each that needs to be averaged (six calculations per facilitator).
Finally, I need to know how many attendees were involved (i.e., the number of rows for that facilitator).
The goal is to have a worksheet where each facilitator has his/her row, the columns give the facilitator's average scores, and the last column says how many attendees.

I attached a sample file with the before and desired after worksheet.

I have hundreds of rows and doing this manually via pivot table will definitely cause errors so any help you can give so it's automated, will be greatly appreciated!

Thank you!
Monica

[Pete_UK]

You can put this formula in cell B2 of the After sheet:

=AVERAGEIF(Before!$A:$A,$A2,Before!C:C)

Copy across to G2, then copy those formulae down. Use this formula in H2:

=COUNTIF(Before!A:A,A2)

then copy down.

Hope this helps.

Pete

[MoniNalu]

Thank you Pete.

I made these changes (added the Sheet name 'Before' to each variable) and I got the formulas to work:
=AVERAGEIF(Before!$A:$A,Before!$A2,Before!C:C)
=COUNTIF(Before!A:A,Before!A2)

To finish, I will
1) copy the names from 'Before' column A to the new 'After' sheet.
2) copy and paste special > values to remove the formula
3) remove duplicates.

Done.

Thank you. This will save me much time.

--Monica

[Pete_UK]

Glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts), you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[LeoTaxi]

More time saving

Please Login or Register  to view this content.

Kind regards
Leo

[MoniNalu]

Leo,

You are awesome -- it's a one click solution! Worked perfectly. I cannot thank you enough for sharing this code with me.

--Monica

[bakerman2]

Since I made it already I'll post it anyway.

Please Login or Register  to view this content.

[MoniNalu]

Thanks for posting it -- I learn when I see different approaches and different solutions. So I'm glad to see your post.

Enjoy the day,
Monica