# Daywise average for the week from Shift-wise data

1. ## Daywise average for the week from Shift-wise data

Hi,

Need a formula to get Day average for a week from shift-wise data.

Since day is divided in to 3 shifts (A, B & C), normal average formula will not work.

An excel file attached with necessary details.

Pl. help.

Thanks,
Nagesh.

2. ## Re: Daywise average for the week from Shift-wise data

You are writing:
Holidays and (Sundays) (Col.R) are to be excluded while calculating average.
But if you look at the expected results, the holidays 1 Jan and 15 Jan (see cells R59 and R60) do not seem to be excluded. How exactly does that work?

3. ## Re: Daywise average for the week from Shift-wise data

For date 1 to 6 result is 7.6. Does it includes all shifts. How 7.6 is achieved. Manually calculation should be shown.

4. ## Re: Daywise average for the week from Shift-wise data

I think the requested result from January 1 to January 6 is 7.8.
After all, January 1 must be excluded (stated in R59) and the total number from January 2 to January 6 is 38. 38 divided by 5 is 7.8.
Unfortunately, the expected result is 7.5.

If January 1 is included, the result is 7.5. Being 45 devided by 6.
The latter is in accordance with the expected result of the OP, but that is not according to the requirement.
After all, If January 1 is included column R is not necessary.

5. ## Re: Daywise average for the week from Shift-wise data

In F5 copied below.
Formula:
`Please Login or Register  to view this content.`

NOTE:
Holidays were repeated from row 59 and below.
I have deleted those days.

6. ## Re: Daywise average for the week from Shift-wise data

Thank you Mr.Srinivasa Murthy.
1. Holidays were not repeated. Those 10 were the company given holidays except Sundays.
2. Date range given just for information only. The date ranges may repeat for furture months also and the formula give wrong results. Tried week number 6,7,8,9 with same date range and the results are giving some data even though the dates are existing in Row1. Hence the formula to be linked to Col.A (Week Number).

Pl. help.

Thanks,
Nagesh.

7. ## Re: Daywise average for the week from Shift-wise data

If all holidays are correct adjust the range of holidays suitably in countif function. I did not follow your week number explanation. Explain with example. Upload file showing problem.
Week number 1 is Between 1st to 6th date in same row. formula works for those dates in B and C column.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1