Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Excel Formula

  • 25-11-2019 05:30PM
    #1
    everypenny
    Registered Users, Registered Users 2 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

  • #2
    28064212
    Registered Users, Registered Users 2 Posts: 11,087 ✭✭✭✭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



  • #3
    coylemj
    Registered Users, Registered Users 2 Posts: 25,914 ✭✭✭✭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.


  • #4
    KAGY
    Registered Users, Registered Users 2 Posts: 1,077 ✭✭✭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