Holiday Pay calculation for 52 weeks

[kristaina]

Hello,

I need to work out the average hourly rate for employee's holiday pay over 52 weeks.

I have created a table where I record all employee pay week by week, I have data worth of 70 weeks and another table where I record worked hours for that week. Average holiday pay would be Pay over 52 weeks/ worked hours over 52 weeks

B5:AX5 = pay
B22:AX22 = hours

I have created the formula, but how can I set it to calculate only 52 weeks?

=IFERROR(((SUM(B5:AX5))/(SUM(B22:AX22))),0)

Thank you in advance!

 

[etaf]

column B to column AX is only 49 columns NOT 52 columns and so not 52 weeks
you can use
=SUM(INDEX(B2:AX2,0,COLUMNS(B2:AX2)-51):INDEX(B2:AX2,0,COLUMNS(B2:AX2)))
where the -51 is the number of weeks +1 so 52
see here

Sum Last N Columns - Excel University

Easily and accurately calculate the sum of the last N columns in Excel with this step-by-step guide by Excel University.

www.excel-university.com

Butr in your data it will give an ERROR as its only 49 weeks

Not sure I understand the 9 have 70 weeks of data
then the range should be 70 column would be B to B2