Hi I am using the formula average(start:end!b4) to calculate average for
sales people. The problem I have is all the sales person's names are not in
the same order in Column A for every sheet.

How do I write a formula to calculate average of all 4 sheets if the sales
person's name is not in the same order without moving and copying it over to
the same line etc.

Thanks

Fawn

Have you considered scrapping the Average function and using vlookups? You
could

=SUM(vlookup(a1,sheet1!a1:b10,2,false),vlookup(... ),...)/4

I hope this is helpful.

Erin


"Fawn" wrote:

Hi I am using the formula average(start:end!b4) to calculate average for
sales people. The problem I have is all the sales person's names are not in
the same order in Column A for every sheet.

How do I write a formula to calculate average of all 4 sheets if the sales
person's name is not in the same order without moving and copying it over to
the same line etc.

Thanks

Fawn

Can I do this if all four quarters are on different worksheets?

Thanks

Fawn


"Erin" wrote in message
...
Have you considered scrapping the Average function and using vlookups?
You
could

=SUM(vlookup(a1,sheet1!a1:b10,2,false),vlookup(... ),...)/4

I hope this is helpful.

Erin


"Fawn" wrote:

Hi I am using the formula average(start:end!b4) to calculate average for
sales people. The problem I have is all the sales person's names are
not in
the same order in Column A for every sheet.

How do I write a formula to calculate average of all 4 sheets if the
sales
person's name is not in the same order without moving and copying it
over to
the same line etc.

Thanks

Fawn

Sure!

=SUM(vlookup(a1,sheet1!$a$1:$b$10,2,false),vlookup (A1,sheet2!,$a$1:$b$10,2,false),vlookup(a1,sheet3! $a$1:$b$10,2,false),vlookup(A1,sheet4!,$a$1:$b$10, 2,false))/4

Where A1 on your summary sheet is the name of sales person, and sheet1 has
the 1st quarter sales with the sales person in column A and their sales in
column B. Then sheet 2 is the second quarter, etc.

I am sure there is a more concise method one of the experts can think of,
but this will work.



"Fawn" wrote:

Can I do this if all four quarters are on different worksheets?

Thanks

Fawn


"Erin" wrote in message
...
Have you considered scrapping the Average function and using vlookups?
You
could

=SUM(vlookup(a1,sheet1!a1:b10,2,false),vlookup(... ),...)/4

I hope this is helpful.

Erin


"Fawn" wrote:

Hi I am using the formula average(start:end!b4) to calculate average for
sales people. The problem I have is all the sales person's names are
not in
the same order in Column A for every sheet.

How do I write a formula to calculate average of all 4 sheets if the
sales
person's name is not in the same order without moving and copying it
over to
the same line etc.

Thanks

Fawn