Formula to calculate rate per hour?

[tcochr]

Ok, so when I work jobs that typically take 3-4 hours and I will make $54-$72. But lets say I finish the job in 2 hrs 15 mins or 3 hrs 45 mins. How do I keep a running tally of my hourly rate? and a running overall average of my total hourly rate?

for example...

column A
2:15
3:45
2:09
..etc

column B
$54
$72
$54
..etc

column C
= rate per hour in currency


cell D1
=overall average of hourly rate



thank you

[AliGW]

In C1:

=A1*B1

In D1:

=AVERAGE(C1:C50)

Change the length of the array to suit.

[63falcondude]

Hello and welcome to the forum. With headers in row 1, try these:

C2 =B2/(HOUR(A2)+MINUTE(A2)/60)

Overall avg =AVERAGE(C:C)

[shg]

	A	B	C	D	E
1		Time	Rate	Pay
2		2:15	$ 54.00	$ 121.50	D2: =B2*C2*24
3		3:45	$ 72.00	$ 270.00
4		2:09	$ 54.00	$ 116.10
5	Total	8:09		$ 507.60	B5: =SUM(B1:B4)
6	Avg Rate		$ 62.28		C6: =D5/B5/24

[tcochr]

Wow! your responses are awesome. I appreciate your assistance. However, I failed to advise that my total pay for the work won't exceed $54 for 3 hrs. Its a base pay of basically $18 an hour. So I am trying to calculate my hourly rate based on me completing the job before 3 hrs. So if I complete the job in 2 hrs my total pay will still be $54 but I technically made $27 an hour

[63falcondude]

See post #3. Does this not work for you?

[tcochr]

No I don't believe so. Column D is too high. I am only making $72 for 4 hrs worked. 72/4= 18 hr. I want to keep track of my hourly rate if I am completing the job before 4 hrs. So if I finish a 4 hr job in 2 hrs and 45 mins, Make sense?

[tcochr]

Column C is NOT the hourly rate, in post 3. That is my final pay. I am simply trying to calculate my rate for completing the job prior to 3-4 hrs.

[63falcondude]

Column C is NOT the hourly rate, in post 3. That is my final pay. I am simply trying to calculate my rate for completing the job prior to 3-4 hrs.

Yes, I understand. With the sample data from post #1, you would be making $24/hr, $19.20/hr, and $25.12/hr respectively.

Let's take the first one for example. If you make $54 in 2 hrs 15 min, that's $54/2.25hours or $24/hr. Same idea for the rest.

Not sure what I'm missing here...

[shg]

	A	B	C	D	E
1		Time	Pay	Rate
2		2:15	$ 54.00	$ 24.00	D2: =C2/B2/24
3		3:45	$ 72.00	$ 19.20
4		2:09	$ 54.00	$ 25.12
5	Total/Avg	8:09	$ 180.00	$ 22.09	B5: =SUM(B1:B4)

[tcochr]

Awesome! thank you SHG. to the others as well.

Is cell D5 and B5 an average?? I don't see the formula for that

[shg]

Is cell D5 and B5 an average?? I don't see the formula for that

The green formula is entered in the first green cell and copied down. The blue formula is entered into the first blue cell and copied right, then formats adjusted appropriately.

[Richard Buttrey]

Cell D5 is the average rate, B5 & C5 are the total times and pay.

I'd be inclined to put the totals currently in B5 & C5 in a new total row 2 above the data and make the formula

=SUM(B3:B102)

or a range sufficiently large to cover all your data then you can just add new jobs below.

[tcochr]

Thank you, 63falcondude! I am a complete Excel dummy. I am trying to make a basic spreadsheet that is wayyy over my head.

[tcochr]

I was having a difficult time in Excel converting the time to hours and minutes

[tcochr]

I inputted shg formula in my Excel doc and Im getting a result of $0.00

=IFERROR(D11/C11/24,0)

D11= 5:15
C11= $126.00

I need the IFERROR and ,0 to display a $0.00 in the fields I do not have any values inputted instead of receiving the #DIV/0! error.

What is wrong here?