using the formula Average(start:end!b4)

[Fawn]

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

 

[Guest]

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]

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

Thanks

Fawn

 

[Guest]

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.