Calculate the Average of n weeks

[Tom0203]

Hi,

I am trying to calculate the average pay per week to calculate the holiday pay. For me to do that I need TOTAL PAY FOR THE LAST 52 WEEKS / 52 = AVERAGE PAY PER WEEK.
This post (How do I continue to take the sum of the last 50 non-zero numbers?) is solving the first part of the calculation which return the total of last 52 weeks pay. I need help for second part of the calculation which is the number of weeks included in total calculation. This is because not all employees have 52 weeks pay.

I have uploaded an image

May thanks for your help

 

[jasonb75]

Try using the same formula, changing the last reference E2:E160 in the formula to 1

In theory it could be as simple as using

=MIN(COUNT(E2:E160),52)

but there is not enough detail to be able to say for certain.

 

[Tom0203]

Try using the same formula, changing the last reference E2:E160 in the formula to 1

In theory it could be as simple as using

=MIN(COUNT(E2:E160),52)

but there is not enough detail to be able to say for certain.

Many thanks. That for sure solve my issue.

Another change in this formula (How do I continue to take the sum of the last 50 non-zero numbers?) I need to make is to stop the formula to go as far as 104 weeks back (including unpaid weeks) to get the pay for last 52 weeks. The form I have has more than 104 weeks. Some weeks are paid some are unpaid.

 

[jasonb75]

The obvious thing would appear to be limiting the formula to 104 rows (it is currently looking at 159 rows). As before, there is not enough detail in your post to provide a definite answer.

How do we know where the latest week is entered? Should we assume that the last entry in E2:E160 is current? Doing this could mean that you are looking back more than 104 weeks from the current date if the most recent weeks are unpaid.

Should we cross reference the pay period dates in the table? If so where are those dates? How do we know what the most recent pay date should be in order to establish if the most recent weeks have been unpaid?