How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

**bjglendinning** · 12-09-2016, 08:40 AM

Hi all, new to this forum, I have been using Excel as a basic user for many years, but have had no formal training. =sum is about as far as I go!

I run a small taxi business, and I am just about to have two drivers join the business, and I have created a basic spreadsheet that calculates the split in the receipts (they get 40% of the total takings, less tips and extras, I receive 60%, less the cost of fuel). I would like to pay the drivers a guaranteed minimum of £45 per shift, averaged over the shifts that they work each week. For example, if they work three shifts, and take £80, £100 and £150 (for example - total £330), I would like Excel to automatically calculate the amount that they should receive based on a £45 minimum averaged over the week (40% of £330 is £132, but 3 x £45 is £135, so Excel would show their pay as £135 not £132. I hope that that makes sense! I'm thinking the IF command might feature somewhere, but I haven't used this command before.

If this needs to be posted on the commercial side, that's fine, if so please advise how many points I would need to get help. Thanks in advance everybody!

Brian

**etaf** · 12-09-2016, 09:15 AM

yes , probably an IF or MAX / MIN ()
however, if you could upload a sample sheet - then we could assist and give you a formula with correct cell reference

**cytop** · 12-09-2016, 09:21 AM

General answer only, a copy of your worksheet would help to give a detailed answer.

The MAX() function can be used to return the Max value of a range of numbers. For example, if your sheet contains the following values:

	A	B
1	Shifts	3
2	Receipts	330
3	Rate	0.4
4	Min Payment	45
5
6		135

Then the formula

=MAX(B4*B1, B2*B3)

in B6 will return 135.

(Yep, I know I could have organised that data better)

**bjglendinning** · 12-09-2016, 09:33 AM

I'm sorry, I don't know how to upload to this forum, clicking on the file attachment icon above just gives me a blank box I can't do anything with...

**etaf** · 12-09-2016, 09:34 AM

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**bjglendinning** · 12-09-2016, 09:38 AM

Many thanks - file attached

**etaf** · 12-09-2016, 09:49 AM

i'm assuming each day is a shift
and so we can count the number of entries with > 0 values
then we can use that count times £45 to work out the minimum wage for the week
say we put that into cell
I21
=COUNTIF(I9:I15,">0")*45

now in the total we can use
=MAX(I9:I15,I21)

**etaf** · 12-09-2016, 09:52 AM

i'm assuming each day is a shift
and so we can count the number of entries with > 0 values
then we can use that count times £45 to work out the minimum wage for the week
say we put that into cell
I21
=COUNTIF(I9:I15,">0")*45

now in the total we can use
=MAX(I9:I15,I21)

Can Brians value be negative because of diesel ?
if so ,then we need to look at the way we count the shifts

as it would be possible for his share to be exactly the same as the Diesel and so be zero - but he worked a shift

we maybe use one of the cells for the receipts of money in the count range
as NOT blank

**CK76** · 12-09-2016, 09:55 AM

So it's not minimum of £45.00 per shift, but if total earning does not equal # of shift x £45.00 it should be adjusted.

Formula in H19 would be...
=IF(AVERAGEIF(H9:H15,"<>0")<45,COUNTIF(H9:H15,">0")*45,SUM(H9:H15))

If each shift should be minimum of £45.00.
Formula in H9 would be...
=IF(AND(SUM(0.4*(D9+E9))<45,F9<>0),45,SUM(0.4*(D9+E9)))
Copy down to H15.

**etaf** · 12-09-2016, 09:59 AM

we can use the date range and count if not blank
=COUNTIF(C9:C15,"<>")*45

my assumption was that this was worked out for the week Mon-Sun
and so 45 * how ever many days worked - would be the minimum wage you would provide - if the earnings in I where less that that minimum value
and so you want to show the maximum
either the min wage is the maximum or his weekly earnings

**bjglendinning** · 12-09-2016, 10:34 AM

Yes, the minimum earnings for a driver is £45 x number of completed shifts per week, so the driver total and the cost of diesel is deducted from the total received, then the balance is what is owed to Brian (me), if that makes it easier to calculate... Thanks for your help with this ETAF.

**etaf** · 12-09-2016, 10:38 AM

so the driver is in column H

in which case
you can still use
=COUNTIF(C9:C15,"<>")*45
But put in say H21 (or any other cell you like
to count the days worked - based on a date being entered
and then in
H19
EDIT Correction
=MAX(H21,(SUM(H9:H18)))

so that wll either return his earnings from revenues received if greater than the number of days worked * 45 or if less that
days worked *45
for the week

**bjglendinning** · 12-09-2016, 12:03 PM

Many thanks ETAF, this look excellent! I will review it tomorrow and get back to you. Thanks again for your help!

**etaf** · 12-09-2016, 12:04 PM

you are welcome

How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

LinkBack

Thread Tools

Rate This Thread

Display

How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Re: How can I use Excel to work out taxi drivers pay when minumum shift payments apply?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Apply fifo when i am making payments to my vendors

Table that matches work shift hours to work shift codes

What ODBC drivers are installed by Excel 2013

[SOLVED] (urgent help pls) how to apply array formula with ctrl+shift+enter

[SOLVED] Work out Credit card Payments to obtain an end date

Taxi scheduling database using Excel?

Why doesn't CTRL+SHIFT+[ARROW] work 2 highlight in Excel 2003?!

Bookmarks

Bookmarks

Posting Permissions