+ Reply to Thread
Results 1 to 10 of 10

Tip Calculation for Multiple Positions

  1. #1
    Registered User
    Join Date
    01-26-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    4

    Tip Calculation for Multiple Positions

    I am trying to create a spreadsheet to distribute a total tip number. I have multiple different positions that recieve a different percentage of the tips. How can I distribute exactly what my total tips are to these different positions at different positions?

    Example: If my bartender makes $100.00, he tips out the barback 1/3 or 33%. My bartender should make double what the barback makes.

    I have a total tip column, an hours column, and a distributed tip column which needs to reflect my percentage difference, but still equal my total tip column.
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Tip Calculation for Multiple Positions

    Hi sommelier,

    Welcome to the forum.

    Is there any role of hours here ?
    Also there are some tips received by bar tender but there is no name e.g., E20, E21 etc. ?

    I think you just want that if a bartender receives 100, then 33% should be taken out and rest is given back to him / her . correct ?

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    01-26-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Tip Calculation for Multiple Positions

    I have multiple bartenders that worked on this day. They all work different hours, and get paid accordingly. The tips however, when distributed, need to be split amongst all that worked and at different percentages. ie; 67% multiplied by the hours worked for a bartender and 33% multiplied by the hours for a barback. But the total tips need to be sread out amongst all that worked. Also, the cells with the bartender names don't corelate to the cells for what each bar brought in. Their names are in random order of when they got there. It is the total tips that I need to disperse to the staff that worked.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Tip Calculation for Multiple Positions

    It is the total tips that I need to disperse to the staff that worked.
    So this will include all bartenders which I see in the sheet 1 A1:G35 ?

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Tip Calculation for Multiple Positions

    I think this can be done with an pivot table.

    Just a start to show you.

    Tip:

    Don't use merged cells; you get in trouble with it sooner or later.

    Organise your data as a table, then you can use pivot table.

    See the attached file.

    Of course there are more (other) options in the pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    01-26-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Tip Calculation for Multiple Positions

    OK. Thank you both for responding, but I probably need to clarify more.

    I need to take Total Tips for Day(D35) and disperse amongst staff members(E6:E32). These tips need to be paid in column H(H6:H32). Baut also need to be multiplied by the hours worked in column G. These staff members are paid a different % of the Total tips for day based on 2 variables (1 being POSITION -Bartender makes 67% of 100% and Barback makes the other 33% of 100%) (2 being HOURS - Staff members also need to be paid based on the amount of hours they worked compared to another staff member)

    Bartenders need to make twice what a barback makes for the same amount of hours, but if a bartender worked half the hours that a barback worked, than they would make the same based on hours and weight of their tips paid.

    I need to make sure that these tips are dispersed and equal (H35) the same amount as the Total Tips for Day (D35)

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Tip Calculation for Multiple Positions

    See Sheet2.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,450

    Re: Tip Calculation for Multiple Positions

    Does the attached help ?Copy of Event Bar Tip Dispersion.xlsx

  9. #9
    Registered User
    Join Date
    01-26-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Tip Calculation for Multiple Positions

    Almost. My Bartenders are the people that are supposed to make double what the Barbacks make. Not the other way around. It is just backwards as far as the person recieving more.

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,450

    Re: Tip Calculation for Multiple Positions

    I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    Cross posted on excelkey.com
    Last edited by Pepe Le Mokko; 01-27-2013 at 05:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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