Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling average | Excel Worksheet Functions | |||
Modified Average Function | Excel Worksheet Functions | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
AVERAGE and STDEV functions with logic | Excel Worksheet Functions |