Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Formula

Options
  • 25-11-2019 5:30pm
    #1
    everypenny
    Registered Users Posts: 255 ✭✭


    Hi All,


    Looking for some assistance with an Excel Formula.


    I have ten people on a roster where there is one person on call every 24 hr period, seven days a week.


    The bonus payments for Mon - Sat (incl) is approx 25 euro and its 50 for Sunday.


    Can I get excel to create a duty roster whereby everyone gets their fair share of Sundays and the payments are balanced out so that no one person is greatly advantaged/disadvantaged?


    Thanks in advance.


    EP


Comments

  • Options
    #2
    28064212
    Registered Users Posts: 10,634 ✭✭✭✭28064212


    Would the obvious "formula" not just be:
    Person1 - Monday
    Person2 - Tuesday
    Person3 - Wednesday
    Person4 - Thursday
    Person5 - Friday
    Person6 - Saturday
    Person7 - Sunday
    Person8 - Monday
    Person9 - Tuesday
    Person10 - Wednesday
    Person1 - Thursday
    Person2 - Friday
    Person3 - Saturday
    Person4 - Sunday
    ...

    After 10 weeks, everyone will have had each day once

    Boardsie Enhancement Suite - a browser extension to make using Boards on desktop a better experience (includes full-width display, keyboard shortcuts, dark mode, and more). Now available through your browser's extension store.

    Firefox: https://addons.mozilla.org/addon/boardsie-enhancement-suite/

    Chrome/Edge/Opera: https://chromewebstore.google.com/detail/boardsie-enhancement-suit/bbgnmnfagihoohjkofdnofcfmkpdmmce



  • Options
    #3
    coylemj
    Registered Users Posts: 25,445 ✭✭✭✭coylemj


    As long as the number of staff is not a multiple of seven, round-robin is all you need.

    For example, if you had 14 staff and rostered round-robin, everyone would end up on call for the same day of the week on every second week. So Michael would do every second Monday and Anne every second Tuesday etc. And you'd end up with two people who would alternate every Sunday. But as seven is a prime number, any pool of people which is not a multiple of seven can be rostered on a round-robin basis and there will be an even distribution of days allocated to each of them.


  • Options
    #4
    KAGY
    Registered Users Posts: 1,091 ✭✭✭KAGY


    everypenny wrote: »
    Hi All,

    I have ten people on a roster where there is one person on call every 24 hr period, seven days a week.

    EP


    You will end up having to list every day and who's rostered on that day as there will be times that people change or have holidays etc. So then it's just easier to check that everyone has the 5 or 6 Sundays in the year



    I've attached a file with two ways


    Using COUNTIFS - not the s at the end, this allows multiple criteria; it's not available in older versions (2007/2010?)



    Using pivot tables


Advertisement