|
Holiday Pay
How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week. -- johnrann ------------------------------------------------------------------------ johnrann's Profile: http://www.excelforum.com/member.php...o&userid=25295 View this thread: http://www.excelforum.com/showthread...hreadid=387815 |
I assume you have observations for each week of pay. Use the SUMIF
function. I don't have time to find other links for you. I'm sure someone else can. "johnrann" wrote in message ... How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week. -- johnrann ------------------------------------------------------------------------ johnrann's Profile: http://www.excelforum.com/member.php...o&userid=25295 View this thread: http://www.excelforum.com/showthread...hreadid=387815 |
Assuming the data is in A1:A100, use
=SUMPRODUCT(--(LARGE(A1:A100,ROW(INDIRECT("1:12"))))) -- HTH Bob Phillips "johnrann" wrote in message ... How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week. -- johnrann ------------------------------------------------------------------------ johnrann's Profile: http://www.excelforum.com/member.php...o&userid=25295 View this thread: http://www.excelforum.com/showthread...hreadid=387815 |
John,
To get the average - rather than the SUM - then with your data starting in A1, in row 12, (any column), try: =SUM(OFFSET(A1,0,0,12,1))/12 and copy down the column -- HTH Sandy Replace@mailinator with @tiscali.co.uk "johnrann" wrote in message ... How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week. -- johnrann ------------------------------------------------------------------------ johnrann's Profile: http://www.excelforum.com/member.php...o&userid=25295 View this thread: http://www.excelforum.com/showthread...hreadid=387815 |
Avearge, divide by
MAX(12,COUNTA(A:A)) -- HTH Bob Phillips "Bob Phillips" wrote in message ... Assuming the data is in A1:A100, use =SUMPRODUCT(--(LARGE(A1:A100,ROW(INDIRECT("1:12"))))) -- HTH Bob Phillips "johnrann" wrote in message ... How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week. -- johnrann ------------------------------------------------------------------------ johnrann's Profile: http://www.excelforum.com/member.php...o&userid=25295 View this thread: http://www.excelforum.com/showthread...hreadid=387815 |
| All times are GMT +1. The time now is 02:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com