Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

How to stop excel from rounding when multiplying by 48

Copper Contributor

I am ultimately trying to determine the hourly cost. I converted HH:MM to decimal (hh:mm*24) then multiplied that by the $48 per hour. The issue is when I multiply by 48, the final product is rounded to a whole number. I would like the round to the nearest cent. I have tried multiple formulas =Round(cell,2), and changing the cell formatting, copying and pasting the product of the conversion as a number, but anytime I multiply by 48 (any other number except 48 works), the number is rounded to the nearest whole number.  Please help. 

 

 

4 Replies

@ahay_03 

It depends on the times. If they are a multiple of 5 minutes, the result will indeed be a whole number, but not otherwise.

S1252.png

@Hans Vogelaar Thank you for your response. Why would 5mins matter? For instance 20 minutes = .33; why is it 16.00 and not 15.84? What formulas did you use? This is what I am getting.

 

ahay_03_0-1648472366073.png

 

best response confirmed by Grahmfs13 (Microsoft)
Solution

@ahay_03 

Because 20 minutes is not exactly 0.33. It is 20/60 = 1/3 of an hour (0.3333333... decimal).

1/3 * $48 = $16.

 

All your times are whole multiples of 0:05. 5 minutes = 5/60 hour = 1/12 hour.

1/12 * $48 = $4, so all the amounts are whole multiples of $4.

Oh my goodness, I can't believe I didn't realize that. Thank you so much; I have been working on this for weeks trying to figure it out what I was doing wrong.
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@ahay_03 

Because 20 minutes is not exactly 0.33. It is 20/60 = 1/3 of an hour (0.3333333... decimal).

1/3 * $48 = $16.

 

All your times are whole multiples of 0:05. 5 minutes = 5/60 hour = 1/12 hour.

1/12 * $48 = $4, so all the amounts are whole multiples of $4.

View solution in original post