Service Charge Formula

[aaronb110]

Hi All

I manage a restaurant and am trying to put together a spreadsheet which will allow me to accurately calculate the how much of the service charge is paid to each employee.

The easy part is that we use a points system where by each job role is assigned a certain number of points ie. Servers 2 points, chefs 4 etc and we work out the value of one point and then multiply that to get each persons payment. However this is causing problems as every member of staff does different hours, so currently someone who works 10 hours a week, gets the same share as someone who does 50 hours a week.

My question is how can I work out the payment based on the points AND the total hours worked? I did try to get a value per hour and multiply that by hours worked, and then multiply by the points value however the final figure was obviously much higher than the total pot of service charge which it can not be. The total payment must equal the service charge pot.

I hope that makes sense!

Look forward to your help guys.

 

[gaz_chops]

Hi Gaz,

It works on my machine, but you have to enter it as an array formula (if you want to enter it as a single formula).

To my mind, my solution has the advantage of giving as much weight to the points as it does the hours, whereas simply adding the hours and points significantly reduces the impact of the points on the reward given to each staff member.

Regards

Hi Peter,
I edited my post, I realised I had the wrong ranges when I copied your formula.
I agree, I think yours is better weighted, I was keeping it simple till the OP commented

Gaz

 

[pjmorris]

Hi Gaz, Glad you got it to work - we must have been typing/editing at the same time!!

Regards