calculating per hour earnings for drivers with a few variables

HOTTRUCK

New Member
Joined
Mar 15, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. MacOS
I am trying to calculate how much a driver actually makes during a delivery based on different vehicle types. I have a calculator that factors in a few things and I have been manually running the numbers through it. Could anyone provide some help on a formula that I can plug into this sheet to perform this task? Here are the relationships I am trying to create in the equation.
AN2=Column AB
AO2=Column AC
AP2=Column AE
These variables get input into the calculator and i would like for column AJ to populate the result (Column AT)
The problem I am having is making column AJ give me the result based on the driver vehicle (Column AA)
im new here so Let me know if I didnt get the sheet put in here correctly. Im on a mac.

delivery data 3-13.xlsx
AAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1Driver VehicleDriver PayTotal MilesTotal MinutesTotal hoursMiles DIFMilutes DIFHours DIFPER HOURACT per hourpaymileshours
2MOTORCYCLE83.1559.9106.01.859.9106.01.8 $ 47.07 39.25831152
3MOTORCYCLE22.2135.549.00.80.8-11.0-0.2 $ 27.19 18.65payMilesgastollshoursMPGper hourearningsgas price
4MOTORCYCLE44.3535.359.01.0-29.7-35.0-0.6 $ 45.08 37.06CAR $ 83.00 11512.50 $ - 2.0023 $ 35.25 $ 70.50 $ 2.50
5TRUCK40.1271.564.01.10.9-24.0-0.4 $ 37.60 29.03TRUCK $ 83.00 11517.97 $ - 2.0016 $ 32.52 $ 65.03 $ 2.50
6CAR2177.662.01.0-14.5-45.0-0.7 $ 20.31 11.48MOTORCYCLE $ 83.00 11523.96 $ - 2.0012 $ 29.52 $ 59.04 $ 2.50
7CAR29.9212.817.00.312.817.00.3 $ 105.60
8CAR27.843357.01.03357.01.0 $ 29.31
9TRUCK46.1923.662.01.022.661.01.0 $ 44.70
10MOTORCYCLE39.9227.659.01.027.659.01.0 $ 40.60
11MOTORCYCLE39.9243.199.01.714.470.01.2 $ 24.19
Sheet1
Cell Formulas
RangeFormula
AC2:AC11AC2=N2+O2+S2
AD2:AD11AD2=W2+X2+Y2+Z2+(R2/60)
AE2:AE11AE2=AD2/60
AF2:AH11AF2=AC2-P2
AI2:AI11AI2=F2/AE2
AN4:AO4AN4=AN2
AP4:AP6AP4=(AO4/AS4)*AV4
AN5:AO5AN5=AN2
AN6:AO6AN6=AN2
AT4:AT6AT4=AU4/AR4
AU4:AU6AU4=AN4-AP4-AQ4
AR4AR4=AP2
AR5AR5=AP2
AR6AR6=AP2
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Hi, and Welcome to Mr. Excel!

Firstly, well done on using the forum tools to lay out your project, enabling folks to have a good idea what you already have.
Secondly, well done on trying to accurately describe what you already have, and what you're trying to achieve; many posters don't, which ends up in forum helpers having to send numerous posts to-and-fro to try and crow-bar out of the poster what they're trying to do.
Thirdly, rest assured, someone CAN help you with your project; there are some very, very switched-on Folk, here (of which I'm not one, unfortunately for you!).

That having been said, I'm having difficulty understanding some of what you actually want to achieve, and I'm guessing that this is why nobody's replied yet!

You say that you're "trying to create a relationship, where AN2=Column AB..." etc etc
I don't yet understand how the Pay cell in AN2, can equal a whole column. AN2 currently displays "83." Is this taken from cell AB2 (83.15) and trimmed, or rounded down? You'll need to find a different way of explaining what you want to achieve, here. Do you mean, for example, that you want AN2 to be capable of having the AB (Driver Pay) column values populated into it, so that you could pick a Driver Pay figure from a list of the Col AB values?

I'm also having difficulty understanding your requirement for "...column AJ to populate the result (Column AT)."
Could you try and explain this need, in a different way, perchance?

Please don't take my points/questions as criticism - they're not - just trying to help you to get over to the members what you want, so that you can get help, and will therefore want to come back for more, in the future.

One other point - ref your request to let you know if you got the sheet put in correctly; basically, yes, -my only comment would be that because your sheet image is so large, I've found myself having to scroll a lot, whilst trying to work out what's where. It may have helped on this occasion, after having got your image onto your post, to have re-sized it to a smaller area (just grab a corner & drag - the same way you would with re-sizing anything else.
That having been said, I'm a Windows user for this kind of thing, so don't know what you can & can't do on a Mac, in this regard.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,574
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top