1

I am attempting to create an Excel sheet that provides a few calculations based on three things:

  • Hours turned by specific technician (4 different techs)
  • Amount of hours worked by each tech (up to 8 hours)
  • Hours paid to each flat rate tech (3 flat rate techs)

I have 3 tables set to calculate each total based on their separate numbers and combined productivity outcomes. The problem I am having is for the hours paid to each tech.

If the hours paid to the team is 30 then each tech would be paid 10 hours IF each tech has worked 8 hours. I can't seem to figure out how to write the equation that will read the data in the hours worked chart and give me a total in the paid table.

Orange Team Daily Sheet

LPChip
  • 59,229
  • 10
  • 98
  • 140
Hans Riegner
  • 11
  • 3
  • 1
    Hello, what did you try? – Destroy666 Jul 17 '23 at 18:56
  • Well I have used the basic add and divide for certain cells. So if tech A turns 5 hrs, tech B turns 15 hours and tech C turns 10 then add the total of those and divide evenly. The problem I'm having is turning that into an equation that divides evenly based on the amount of hours the technician was working that day. So if tech A turned 5 hours but only worked a half day, or 4 hours, then divide evenly based on that. – Hans Riegner Jul 17 '23 at 19:00
  • Please _show_ us what you tried. Sample data an expected outcome would also be useful – cybernetic.nomad Jul 17 '23 at 19:07
  • I hope that screenshot helps! – Hans Riegner Jul 17 '23 at 19:29
  • 1
    It does a bit but what would really help is specifying what you want, e.g. do what with what cells. In the question, you make references to tables that aren't identified in your screen shot — and incidentally, a screen shot is less than ideal since you can't copy/paste anything. And for me at least, I have no clue what a turning an hour is. – Mockman Jul 18 '23 at 07:17
  • These are fair points! I'm sorry but I'm new to this excel thing! Thank you for your patience with me! When a flat rate tech completes a job, for example a set of brakes or an oil change, it pays him or her a certain amount of time. That flat rate time could be different than how long the technician physically worked on that job. The pad and rotor replacement could pay the technician 2 hours when they only took an hour to complete. It would also only pay 2 hours of the job took 5 hours if it was only sold to the customer for 2 hours. On a team system, the hours are split evenly. – Hans Riegner Jul 18 '23 at 10:52
  • I would send the Excel sheet but my employer does not allow that for security reasons unfortunately. When a flat rate hour is turned it is put into the chart on the lower right. When the tech is at work for their normal shift, 8 hours is placed in the lower left. And when the day is complete, the techs turn in their hours and those are split evenly. That is what the top chart is for. If one tech turns 15, one turns 5 and one turns 10, then they will evenly split 10 hours a piece. That changes when one tech is not at work the full day, maybe for a doctor's appointment or something. – Hans Riegner Jul 18 '23 at 11:00
  • The lower left column could read 4. So instead of 10 hours on that day, the tech would be paid 5 and the other 5 extra hours would split 2.5 a piece to the other 2 techs. That's the equation I'm having trouble with – Hans Riegner Jul 18 '23 at 11:02
  • This question is a complete mess now. I'd recommend reading https://superuser.com/help/how-to-ask and checking how other high-voted questions about Excel look, else this is likely to get closed. – Destroy666 Jul 18 '23 at 11:35
  • I'm confused and would like to clear anything up, what makes the question a mess? – Hans Riegner Jul 18 '23 at 12:31
  • ".. the data in the hours worked chart and .. in the paid table." >> where is the **hours worked** table & the **paid** table ? – p._phidot_ Jul 21 '23 at 13:48

0 Answers0