Create a Formula for this

[mn072065]

Hi All, Happy Holidays

Need some help on a project. Trying to calculate this for someone, if they work weekly # of hours at a rate of $13, but now we change the rate for $15, now this guy wants to keep paying the same weekly amount dollars from the $13 rate, when they make the $15 amount. So he would have to reduce the hours ???? to come up with the same rate of $13 an hour. Can i formula be created for that. Please keep it simple. Thanks appreciate it.

NAME Acutal 40 Hrs ot hours ot rate ot rate total Total Reg Grand Total
spongebob $13.00 40 8 $19.50 $156.00 $520.00 $676.00
spongebob $15.00 40 8 $22.50 $180.00 $600.00 $780.00
calculated $15.00 33 8 $22.50 $180.00 $495.00 $675.00

 

[Logit]

.

	F​	G​	H​	I​	J​	K​
11​	Pay Rate​	Hrs Reduction​	Total Hrs Work​	Diff From Previous​	Actual Hrs​	Formula​
12​	$16.00​	-7.5​	32.5​	2.25​	32.50​	=520/16​
13​	$15.00​	-5.25​	34.75​	2.5​	34.67​	=520/15​
14​	$14.00​	-2.25​	37.25​	2.75​	37.14​	=520/14​
15​	$13.00​	0​	40​	0​	​	​

Seems to be declining by 1/4 hr for each dollar raise in pay.

 

[mn072065]

thanks logit so how can I do a spreadsheet to see diff hours for each person.

 

[Logit]

.
Provide an example of employees names, their pay rate for 40 hrs and what will be their new pay rate.

You want all of these employees to earn the same weekly amount at the higher pay rate as they did for lower pay rate but you want to reduce their hours to accomplish this. Correct ?

(The names can be false. Like Emp1, Emp2, etc.)

 

[mn072065]

Thank you so much

NAME Acutal 40 Hrs ot hours ot rate ot rate total Total Reg Grand Total
BARNEY $13.00 40 8 $19.50 $156.00 $520.00 $676.00

NAME Future 40 Hrs ot hours ot rate ot rate total Total Reg Grand Total
BARNEY $15.00 40 8 $22.50 $180.00 $600.00 $780.00

 

[Logit]

.
Here is the chart. You'll need to decide how to apply it to your project :

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​	Hrly Rate​	Total Hrs​	Equals​	Salary​	O/T Rate​		Pay Rate​	Hrs Reduction​	Total Hrs Work​	Diff From Previous​	Actual Hrs​	Formula​
2​	$13.00​	40.00​	=​	$520.00​	$19.50​		$16.00​	-7.5​	32.5​	2.25​	32.50​	=520/16​
3​	$14.00​	37.14​	=​	$520.00​	$21.00​		$15.00​	-5.25​	34.75​	2.5​	34.67​	=520/15​
4​	$15.00​	34.67​	=​	$520.00​	$22.50​		$14.00​	-2.25​	37.25​	2.75​	37.14​	=520/14​
5​	$16.00​	32.50​	=​	$520.00​	$24.00​		$13.00​	0​	40​	0​	​	​
6​	$17.00​	30.59​	=​	$520.00​	$25.50​				​	​	​	​
7​	$18.00​	28.89​	=​	$520.00​	$27.00​				​	​	​	​
8​	$19.00​	27.37​	=​	$520.00​	$28.50​				​	​	​	​
9​	$20.00​	26.00​	=​	$520.00​	$30.00​		In B3 copied down column :		=D2/14​	​	​	​
10​	$21.00​	24.76​	=​	$520.00​	$31.50​				​	​	​	​
11​	$22.00​	23.64​	=​	$520.00​	$33.00​		In D2 copied down column :		=B3*A3​	​	​	​
12​	$23.00​	22.61​	=​	$520.00​	$34.50​				​	​	​	​
13​	$24.00​	21.67​	=​	$520.00​	$36.00​		In E2 copied down column :		=A2*1.5​	​	​	​
14​	$25.00​	20.80​	=​	$520.00​	$37.50​				​	​	​	​
15​	$26.00​	20.00​	=​	$520.00​	$39.00​				​	​	​	​
16​	$27.00​	19.26​	=​	$520.00​	$40.50​				​	​	​	​
17​	$28.00​	18.57​	=​	$520.00​	$42.00​				​	​	​	​
18​	$29.00​	17.93​	=​	$520.00​	$43.50​				​	​	​	​
19​	$30.00​	17.33​	=​	$520.00​	$45.00​				​	​	​	​