+ Reply to Thread
Results 1 to 7 of 7

Daywise average for the week from Shift-wise data

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    417

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,313

    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. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,430

    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.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,313

    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.
    Last edited by HansDouwe; 02-09-2024 at 09:23 AM.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,430

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

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

    NOTE:
    Holidays were repeated from row 59 and below.
    I have deleted those days.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 02-09-2024 at 11:21 AM.

  6. #6
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    417

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

    Thank you Mr.Srinivasa Murthy.
    My comments are given below:
    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. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,430

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Day and Shift wise allocation from Roster / employee shift schedule
    By Ravi_Kadu in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-11-2021, 07:40 PM
  2. Shift Wise Productivity Report to pay Shift Allowance
    By ramesh_cl1981 in forum Excel General
    Replies: 2
    Last Post: 01-01-2021, 01:38 PM
  3. Shift Wise Productivity Report to pay Shift Allowance
    By ramesh_cl1981 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2020, 02:38 AM
  4. [SOLVED] how to get week day wise, date wise value from raw data
    By emmr in forum Excel General
    Replies: 5
    Last Post: 09-04-2020, 07:21 AM
  5. Replies: 4
    Last Post: 05-01-2020, 01:10 AM
  6. Formula to insert shift daywise
    By akashmenon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2014, 01:43 AM
  7. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM

Bookmarks

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