I want to look up for a value in a coloumn and get the average of the row in
front of that particular cell.

What formule should I derive?
Can some one help on this.

"Jeffrey" wrote:
I want to look up for a value in a column
and get the average of the row in front of that particular cell.

One venture ..

Assuming source table in Sheet1,
with lookup values listed in A3 down,
and "corresponding" numeric values in B2 across/down

In another sheet,
with the same lookup values listed in A3 down

Put in B1:
=AVERAGE(OFFSET(Sheet1!$1:$1,MATCH(A3,Sheet1!A:A,0 )-2,))
B1 will return the average for the row above the corresponding lookup value
in Sheet1 (this is what you wanted, right?). Copy B1 down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

This is not what I was looking for.
I want to calculate the average of scores achieved by a guy.

for e.g:
A B C D E
1 2 3 4
smith 98 65 98 96
donald 97 94 92 93
alex 91 90 89 97
ronald 91 94 95 97
jose 94 95 92 90
brad 94 91 95 94
donald 98 65 98 96
brad 97 94 92 93
jose 91 90 89 97
alex 91 94 95 97
davis 94 95 92 90
smith 94 91 95 94

average of Scores achieved by alex(if his name is there in column A)

What formulae should I derive to calculate this?


"Max" wrote:

"Jeffrey" wrote:
I want to look up for a value in a column
and get the average of the row in front of that particular cell.

One venture ..

Assuming source table in Sheet1,
with lookup values listed in A3 down,
and "corresponding" numeric values in B2 across/down

In another sheet,
with the same lookup values listed in A3 down

Put in B1:
=AVERAGE(OFFSET(Sheet1!$1:$1,MATCH(A3,Sheet1!A:A,0 )-2,))
B1 will return the average for the row above the corresponding lookup value
in Sheet1 (this is what you wanted, right?). Copy B1 down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

This is not what I was looking for.

That's because that's how you had described it in your orig. post.

Now that you've clarified, just tweak it a little,

use instead:
=AVERAGE(OFFSET(Sheet1!$1:$1,MATCH(A3,Sheet1!A:A,0 )-1,))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeffrey" wrote in message
...
This is not what I was looking for.
I want to calculate the average of scores achieved by a guy.

for e.g:
A B C D E
1 2 3 4
smith 98 65 98 96
donald 97 94 92 93
alex 91 90 89 97
ronald 91 94 95 97
jose 94 95 92 90
brad 94 91 95 94
donald 98 65 98 96
brad 97 94 92 93
jose 91 90 89 97
alex 91 94 95 97
davis 94 95 92 90
smith 94 91 95 94

average of Scores achieved by alex(if his name is there in column A)

What formulae should I derive to calculate this?

Hi Max,

This formulae is still of no help...:(
Given below is a more elaborate exaple: if you could help.......

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



Average Scores
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 and if the name is not the then the
answer should be NA or also if there is no score.
I'm doing it on one sheet only. i.e. sheet1


"Max" wrote:

This is not what I was looking for.

That's because that's how you had described it in your orig. post.

Now that you've clarified, just tweak it a little,

use instead:
=AVERAGE(OFFSET(Sheet1!$1:$1,MATCH(A3,Sheet1!A:A,0 )-1,))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeffrey" wrote in message
...
This is not what I was looking for.
I want to calculate the average of scores achieved by a guy.

for e.g:
A B C D E
1 2 3 4
smith 98 65 98 96
donald 97 94 92 93
alex 91 90 89 97
ronald 91 94 95 97
jose 94 95 92 90
brad 94 91 95 94
donald 98 65 98 96
brad 97 94 92 93
jose 91 90 89 97
alex 91 94 95 97
davis 94 95 92 90
smith 94 91 95 94

average of Scores achieved by alex(if his name is there in column A)

What formulae should I derive to calculate this?

See this quick sample construct:
http://cjoint.com/?ferxKhY1B4
Jeffrey_wks.xls

Array-entered* in C59:
=IF(ISERROR(AVERAGE(IF($B$2:$B$53=$B59,C$2:C$53))) ,"NA",AVERAGE(IF($B$2:$B$53=$B59,C$2:C$53)))
*Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER

Copy C59 across/down to E74
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeffrey" wrote:
Hi Max,

This formulae is still of no help...:(
Given below is a more elaborate exaple: if you could help.......

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



Average Scores
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 and if the name is not the then the
answer should be NA or also if there is no score.
I'm doing it on one sheet only. i.e. sheet1

Thanks Max,

It was of great help.
Now if i want an avergae of all the cells in C59(for the first name) i.e.
from $c$2:$c$53 in one cell only anf if I want to leave the cells which are
blank or have a TEXT in them(other than a number)

What formule should I write.

Looking forward for some more help from you.

"Max" wrote:

See this quick sample construct:
http://cjoint.com/?ferxKhY1B4
Jeffrey_wks.xls

Array-entered* in C59:
=IF(ISERROR(AVERAGE(IF($B$2:$B$53=$B59,C$2:C$53))) ,"NA",AVERAGE(IF($B$2:$B$53=$B59,C$2:C$53)))
*Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing ENTER

Copy C59 across/down to E74
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeffrey" wrote:
Hi Max,

This formulae is still of no help...:(
Given below is a more elaborate exaple: if you could help.......

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



Average Scores
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 and if the name is not the then the
answer should be NA or also if there is no score.
I'm doing it on one sheet only. i.e. sheet1

Isn't that exactly what I've responded earlier?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeffrey" wrote in message
...
Thanks Max,

It was of great help.
Now if i want an avergae of all the cells in C59(for the first name) i.e.
from $c$2:$c$53 in one cell only anf if I want to leave the cells which
are
blank or have a TEXT in them(other than a number)

What formule should I write.

Looking forward for some more help from you.