[Solved] Calculating payments based on hours and percentages

Discuss the spreadsheet application

[Solved] Calculating payments based on hours and percentages

Postby DaWat » Tue Jul 14, 2020 7:20 pm

I am affiliated with a business that pays subcontractors based on the job. The total job payment is a set amount, but how much goes to which contractor is variable based on how much time the contractor put in, and what their value relative to the other contractor is. I'm having a difficult time writing a formula that can calculate that without getting into circular reasoning.

For example I have $4000 to pay out on a Job. Trinity had 20 hours, TFT had 40 hours, and Hanson had 40 hours. Trinity and TFT are paid at the same rate, Hanson is paid at half of whatever they are making. I can manually enter percentages until I get that figured out, but I would like to have a formula to do it for me, so that when I enter a new total price and everyone's hours and percentage, it calculates it correctly.

This is very easy to do when everyone is getting paid at the same rate, but when some contractors are more valuable than others, and their percentage of the hours are fluctuating, I haven't yet gotten it.

I attached my manual version.
Attachments
Pay Scale Calculator.ods
(12.7 KiB) Downloaded 35 times
Last edited by Hagar Delest on Wed Jul 15, 2020 7:52 am, edited 1 time in total.
Reason: tagged solved.
LibreOffice 6.2 on Windows 10 Home
DaWat
 
Posts: 2
Joined: Tue Jul 14, 2020 6:58 pm

Re: Calculating payments based on both hours and percentages

Postby FJCC » Tue Jul 14, 2020 9:33 pm

Take a look at the calculations i columns I - K. The Final Pay preserves the 50% ratio between Trinity and TFT based on the hours worked and the 0.62 ratio between TFT and Hanson based on the pay %.
Attachments
Hours.ods
(12.77 KiB) Downloaded 36 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7968
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculating payments based on both hours and percentages

Postby lader » Tue Jul 14, 2020 9:39 pm

I used the sumproduct function
Pay Scale Calculator Test.ods
(13.45 KiB) Downloaded 35 times
LibreOffice 6.4.7.2 on Ubuntu 20.04.4 LTS
lader
 
Posts: 32
Joined: Mon Jul 02, 2018 6:10 pm

Re: Calculating payments based on both hours and percentages

Postby DaWat » Tue Jul 14, 2020 10:51 pm

Thanks FJCC and lader! Both function correctly. I'm not sure I understand either well enough to write it myself yet, but it will work for this sheet! I appreciate it.
LibreOffice 6.2 on Windows 10 Home
DaWat
 
Posts: 2
Joined: Tue Jul 14, 2020 6:58 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests