I'm trying to get an average percentage per client. I attached a copy of my current sheet to give a visual of what I'm trying to do. There is a percent and to the right is a client name or abbreviation. My goal is to calculate an average for the scores in the left columns based on the clients listed in the right columns.


Thanks!

Attached Images

Capture.PNG (46.5 KB, 23 views)

For each client...

=sumif(f:f,[client],e:e)/countif(f:f,[client])

modify the ranges as you see fit.

=sumif(F3:F15,S4A,E3:E15)/countif(F3:F15,S4A)


This is the formula that I'm putting in and I'm getting the following output:

#DIV/0!

Can you take a look at the image I have attached and see if you can provide any additional assistance?

Also can I use wildcards in this formula for the client names?

Thanks!

You would need to put the client name in quotes since it is text.

Alternatively, you could have a list of clients in a table and the formulas the next column over with the formula referencing the address of the client field.

http://www.bluepecantraining.com/por...ifs-functions/

I can see you use Office 2013 so you could try this one (in for example T3 and copy down):

=IFERROR(AVERAGEIFS($A$3:$A$15,$B$3:$B$15,B3,$A$3: $A$15,"<>"),"")

And yes, you can use wildcards for client names. For example

=IFERROR(AVERAGEIFS($A$3:$A$15,$B$3:$B$15,"O*",$A$ 3:$A$15,"<>"),"")

will return 0.831429 which is the average for all clients where name starts with O.

As stated here the formula cannot be copied to the right without manual adjustment of the references because of the client columns.

Ohhhh....AVERAGEIF. I should have thought of that.

Thank you guys so much. So this is where I'm at now... and I'm feeling a little dumb lol


I plugged in this formula:
=IFERROR(AVERAGEIFS($E$3:$E$15,$F$3:$F$15,"*AVE",$ E$3:$E$15,"<>"),"")

and got the correct average for the clients on one week. The issue I'm facing now is how get the average for multiple weeks so that I can get an average per client for the entire month.

Any help is appreciated!!

Thanks!

What do you mean by entire month. Which columns? Please give examples of expected results.

(It would be easier to help if you could upload an Excel file instead of a picture).

Basically what I'm doing is trying to get an average percentage per client for our entire month. Each week has two separate columns containing scores for that week.

I have attached my excel document. Thanks again for all your help!

Attached Files

AverageByClient.xlsx (12.7 KB, 17 views)

What would then be the average percentage for instance for AVE and OAVE for your entire month? Please specify exactly which cells should be included for each of these clients (cf. my request at expected results in my previous message).

For example I need an combined average for all percentages to the left of all clients listed as AVE and OAVE: C5,C6,C8,C11,C13,E5,E6,E8,E11,G6,G8,G9,G11,G13 ect...

I am aware of how to get this manually each month. I'm just trying to get help with a formula that will calculate an average percent based on the percentages to the left of each client name.

I hope that makes since... Please let me know if you need more clarification, sorry.
Again thank you for helping.

I'll dip my toe in again with a possible solution. If there's a more elegant one, perhaps xor knows it.

I don't think one can do AVERAGEIFS over multiple ranges. If so, then you would have to do it for each column of data and then combine. But averages of averages may not be accurate. This is definitely the case here since there are variable counts in the columns which would weight the percentages in columns with fewer counts greater than those with more.

Thus, the most accurate solution I can think of would be to go back to my earlier less elegant formula and expand it over the columns i.e. (sum of sums for each)/(sum of counts for each).

Without further ado, the formula from hell...

Thanks so much for the formula. I tried it out and it doesn't seem to be accurate it is a few percentile off of the actual average. I think we may try to do something to make this work by pasting them in the same column. Unless anyone has a better solution...

Don't know if this can be used but you may want to give it a try.

Attached Files

AverageOfAverages_2.xlsx (15.4 KB, 13 views)

Many thanks xor and gebobs!

You guys rock!!
Thanks for saving me so much time and brain rattling!