Need help modeling data

Hello,

Attached is a a screenshot and active workbook for something I am needing for work.

Basically there is a regular hourly rate, travel rate (half time), and overtime rate (1.5x). Overtime is taxed much higher than regular rates. Travel hours are calculated into your 40 hours first before any other hours.

I am trying to figure out what the ideal about of travel time should be for a certain amount of work for the week.

I can not figure out a way to make lines/data intersect so that you can make comparsions. The only explanation I can form from my data is that the more you travel the less you truly make per hour.

Any tips for the smart people out there?

Welcome to the forum
as nothing is attached,

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

This is not an excel question, but here we go.


travel 7,50

normal 15,00 (minimum 40 hours)

overtime 22,50

If you travel in the first 40 hours you will be lose income 7,50 / hour (normal 15,-- -/- 7,50 travel).

If you travel in the overtime hours you will be lose income 15,-- / hour (overtime 22,50 -/- 7,50 travel).

Since the travel hours are paid less, you have to reduce the travel hours, and if you travel, do it in the first 40 hours (including the travel).