Help Me Calculate a Formula (Average Hourly Rate)

Topic

New Reply

[WSJon5]

Hi all,

I’m back with another Google Sheet request. Please take a look at the below screenshot:

I’ve been able to calculate columns G and H (“average time” and “average pay”); where I need help is column I (“average hourly rate”).

The math is simple: $182 / 4.35 (hours) = $42.82/hour. Divided by 2, that comes to $20.92/hour, or $21 if you round, as I do.

However, the formula I’m using for column I — =sum(H6/G6)/2 — generates $476.14.

Any thoughts on how to fix this?

I suspect the problem is that column G is “4:35” instead of “4.35.”

Thanks so much!

• This topic was modified 8 months, 4 weeks ago by WSJon5.

Reply | Quote

Replies

WSJon5 wrote:

I suspect the problem is that column G is “4:35” instead of “4.35.”

Correct. 4:35 is 4 hours 35 minutes which is 4.58 hours in decimal form not 4.35. You have to convert 4:35 (hours/minutes) to the decimal form which is 4.58 before you can apply the other formula (which is wrong for hourly rate). To do this is very easy.

To your current worksheet insert a column between G and H columns. This column will be Adv Time Decimal and will be your new H column and the old H & I columns will move to the right and become I & J. Format all the cells in this new H added column to a Number with 2 places after decimal.
Using your line 22 as an example enter into cell H22 the following formula:
=HOUR(G22) + MINUTE(G22)/60
For the 4:35 in G22 it will yield 4.58 which is 4.58 hours
Copy this formula and paste the formula in all the cells of this new Column H
Select J22 (which is the Average hourly rate in the old column I) and enter this formula;
=I22/H22
I don’t understand why you divide the actual hourly rate by 2, but if needed for half hour rate the formula can be adjusted.
You don’t need to see the decimal value for the avg hours worked, so select the new H column, right click, and select Hide.
The end result will look like your worksheet but will accurately calculate the Average Hourly Rate.

The attached Excel document has the formulas and the column is not hidden.

Hourly-rate

HTH, Dana:))

1 user thanked author for this post.

WSJon5

Reply | Quote

Perfect — thank you so much!

Reply | Quote

Jon,

I don’t know if Google Sheets has these functions but in Excel you’d convert the hours to minutes, add the minutes, divide to find rate per minute, then multiply by 60.

Actually you could just multiply by 30 and skip the division by 2 but as shown is clear if it is referenced by someone else.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

WSJon5

Reply | Quote

Thanks, RG! I appreciate the screenshot!

Reply | Quote

What value and format are you using in Column E? It should be the decimal equivalent of the time, formatted as HH:MM. Then all calculations work.

As Dana pointed out, 4.58 is the decimal, but as a time value it’s 4.58/24 = 0.19083333 (1 = 24 hours in Excel speak).
Then your calculations become multiplication instead of division.

To enter time without having to use the colon, use zeddy’s converter, or RG’s VBA.

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote

Many thanks, Paul. I’m reluctant to continue tinkering, since this is now fixed. But I’m grateful for your advice!

Reply | Quote

I don’t like making up values when spreadsheets already have a method. Plus I don’t have to remember to convert things to use the values.
Percent is an obvious example. If you type 3.4% the spreadsheet will enter the value 0.034 and format it as percent. Now any calculation is simple.

cheers, Paul

1 user thanked author for this post.

WSJon5

Reply | Quote