Derive a formule for finding the average

J

Jeffrey

I want to derive a formulae with the given below condition.
If the name is available in the agent list, then the average comes up, else NA

For e.g.
A B C D E F G
S. No. Agent Name Call 1 Call 2 Call 3 Call 4 Call 5
1 Akshay Sing Rawat Training Period
2 Amit Sharma Training Period
3 Ankur Chopra 100 100 95 100 emails
4 Anshul Almadi 100 95 95 100 95
5 Bhupender Singh 100 95 100 90 40
6 Chandan Deep Kaur NC 100 100 45 95
7 Charanpal Singh 100 95 100 90 L
8 Deven Gurung 100 100 100 95 100
9 Gaurav Pandoh 100 100 95 95 95
10 Maninder Singh 100 100 100 100 100
11 Mohammed Moddasar 100 95 100 90 0
12 Purva Jain Training Period
13 Sandeep Training Period
14 Suresh Singh FS FS 100 FS 100
15 Utsav Basu 100 95 100 95 100
16 Varun Sharma 100 L L L 90
Average Scores 100 98 99 90 82
S. No. Agent Name Call 1 Call 2 Call 3 Call 4 Call 5
1 Akshay Sing Rawat Training Period
2 Amit Sharma Training Period
3 Ankur Chopra Emails Emails Emails Emails Emails
4 Anshul Almadi 95 90 90 90 100
5 Bhupender Singh 95 100 100 100 95
6 Chandan Deep Kaur 100 100 100 100 95
7 Charanpal Singh 90 100 95 95 100
8 Deven Gurung 100 100 FS FS FS
9 Gaurav Pandoh 100 100 95 95 95
10 Maninder Singh 100 100 95 95 95
11 Mohammed Moddasar 100 100 100 100 95
12 Purva Jain Training Period
13 Sandeep Training Period
14 Suresh Singh 95 L L L L
15 Utsav Basu 100 95 100 95 95
16 Varun Sharma 100 100 95 95 L
Average Scores 98 99 96
S. No. Agent Name Call 1 Call 2 Call 3
1 Akshay Sing Rawat 90 100 43
2 Amit Sharma 90 95 100
3 Ankur Chopra Emails Emails Emails
4 Anshul Almadi 95 100 95
5 Bhupender Singh 95 L 100
6 Chandan Deep Kaur 48 95 100
7 Charanpal Singh 48 100 100
8 Deven Gurung FS 100 100
9 Gaurav Pandoh 100 100 100
10 Maninder Singh 100 95 100
11 Mohammed Moddasar 100 100 100
12 Purva Jain 90 100 90
13 Sandeep L 100 L
14 Suresh Singh L FS FS
15 Utsav Basu 95 100 L
16 Varun Sharma 90 95 100
Average Scores 87 98 94




Akshay Sing Rawat
Amit Sharma
Ankur Chopra
Anshul Almadi
Bhupender Singh
Chandan Deep Kaur in This column I want calculate the average score.
Charanpal Singh
Deven Gurung
Gaurav Pandoh
Maninder Singh
Mohammed Moddasar
Purva Jain
Sandeep
Suresh Singh
Utsav Basu
Varun Sharma


I want to calculate the average score of Chandan Deep Kaur, if the name is
available in the agent name list.
I'm doing it on one sheet only. i.e. sheet1
 
D

Don Guillett

I already answered this for you in another similar post.
This is an ARRAY formula that must be entered using ctrl+shift+enter instead
of just enter
=AVERAGE(IF(A2:A13="alex",B2:E13))
 
J

Jeffrey

Hi Don,

Its giving me an error "Value". on
I'm putting the values in column B(all the names) and is calculating the
value ine average of values if any in cells B2:B53.
=AVERAGE(IF(B2:B53="Bhupender",C2:F53))

Please help.
 
Top