Average of %

U

Uma Nandan

Hi,

I have the following data and would require help in calculating average % as
mentioned in below output.

We have huge data like this and it becomes difficult to calcualted it
manually using filter. Can some one help me with it?

Column A Column B Column C
Name Position Name Accuracy %
Namrata Ahuja Sr. Service Exec 80.00%
Neelam Sharma GWIM Exec 100.00%
Neha Juneja HR Manager 75.00%
Neha Juneja HR Manager 100.00%
Sundu Tamang Risk Analyst 99.63%
Sundu Tamang Marketing-Barclay 97.25%
Dilpreet K Nagi Market Writer 99.00%
Dilpreet K Nagi Market Writer 100.00%

Out put should be:

Column A Column B
Name Average Accuracy %
Namrata Ahuja 80.00%
Neelam Sharma 100.00%
Neha Juneja 87.50%
Sundu Tamang 98.44%
Dilpreet K Nagi 99.50%

Thanks in advance.

Regards,
 
M

Mike H

Hi,


I have assumed your data are in columns A.B and C
Select your list of names then
Data|Filter|Advanced filter
Selecr 'Copy to new location
Check 'Unique values only'
Enter an address to copy to (I used K1)
Click OK

Yoou will get a unique list in K1 down
enter this in L1 and drag down
=AVERAGE(IF($A$2:$A$10=K1,$C$2:$C$10,FALSE))

This is an array formula and must be entered woth CTRL+Shift+Enter and not
just enter. If you do it correctly Excel will put curly brackets around it
{}. You can't type these yourself

Mike
 
P

Pyrite

I think I got the result you are looking for using an array formula.

If names are in column A2 onward and % in column C2 onward and I presume you
are creating another smaller table with name and average %. These can be in A
and B12 onward. As follows:

A B C
1 Name Position Name % Accuracy
2 David Service Manager 80%
3 John etc etc
4 Steve etc etc
5 David etc etc
6 John etc etc
7 John etc etc
8 Steve etc etc
9
10
11 Name Average % Accuracy
12 David
13 John
14 Steve


In cell B12 put {=AVERAGE(IF((A2:A8=A12),C2:C8,""))} and copy it down. This
will give the average for the figures appearing next to name specified in
Column A. The { } must not be entered manually, to achieve this enter the
formula without them but instead of pressing enter on its own press Ctrl,
Shift + Enter all at the same time.

I have limited experience so maybe one of the other more experienced forum
users could quality check this formula, I have tested it and it seems to do
the trick.
 
P

Pyrite

My apologies I left out the $ symbols to keep the range the same, it would
then be {=AVERAGE(IF(($A$2:$A$8=A12),$C$2:$C$8,""))}
 
Top