Average
 

I have a workbook with 3 worksheets. In each worksheet i have a list of
attorneys in column A - the next 12 columns are monthly statistics for each
month and then grand total by person.

The three worksheets contain the data for three different years. for
instance i have one worksheet with the monthly data for 2003, one for 2004,
and one for 2005.

i have taken that data (it was in the form of a pivot table) and changed the
#s to % of rows.

I want to create a fourth worksheet showing the three year average % by
month for each person. I have two issues. First - i was thinking that I
could use a vertical lookup to ensure I have the right data for the right
person - but in that vertical lookup how do I calculate the averages of the
three years of data? Second - what if someone does not appear in all three
worksheets - but started in the second or third year?

Average
 

Try to create a formula as:

=average(vlookup(A4,'2003'!A4:B9,2,0);vlookup(A4,' 2004'!A4:B9,2,0);vlookup(A4,'2005'!A4:B9,2,0))

Marcelo

"MLShef" escreveu:

I have a workbook with 3 worksheets. In each worksheet i have a list of
attorneys in column A - the next 12 columns are monthly statistics for each
month and then grand total by person.

The three worksheets contain the data for three different years. for
instance i have one worksheet with the monthly data for 2003, one for 2004,
and one for 2005.

i have taken that data (it was in the form of a pivot table) and changed the
#s to % of rows.

I want to create a fourth worksheet showing the three year average % by
month for each person. I have two issues. First - i was thinking that I
could use a vertical lookup to ensure I have the right data for the right
person - but in that vertical lookup how do I calculate the averages of the
three years of data? Second - what if someone does not appear in all three
worksheets - but started in the second or third year?

Average
 

MLShef,

You could also, copy all the information for the fourth worksheet and use a
Dinamic table to get the average.


"Marcelo" escreveu:

Try to create a formula as:

=average(vlookup(A4,'2003'!A4:B9,2,0);vlookup(A4,' 2004'!A4:B9,2,0);vlookup(A4,'2005'!A4:B9,2,0))

Marcelo

"MLShef" escreveu:

I have a workbook with 3 worksheets. In each worksheet i have a list of
attorneys in column A - the next 12 columns are monthly statistics for each
month and then grand total by person.

The three worksheets contain the data for three different years. for
instance i have one worksheet with the monthly data for 2003, one for 2004,
and one for 2005.

i have taken that data (it was in the form of a pivot table) and changed the
#s to % of rows.

I want to create a fourth worksheet showing the three year average % by
month for each person. I have two issues. First - i was thinking that I
could use a vertical lookup to ensure I have the right data for the right
person - but in that vertical lookup how do I calculate the averages of the
three years of data? Second - what if someone does not appear in all three
worksheets - but started in the second or third year?