How to use the "base" of a MROUND in a calculation?

First, I am trying to possibly "fix the formula" in B6 so it calculates correctly. Whatever that amount is (in this case 1h11, even though Excel has it as 1h10),

Perhaps we ought to establish exactly what is incorrect about the calculation in B6, because, as near as I can tell, it is correct.

One key with spreadsheets -- especially when dealing with times -- is to carefully distinguish between what you see in the cell and the cell's underlying value. In this case, the underlying value is 1 hour 10 minutes and 30 seconds -- which seems correct to me. B6 is taking the difference between B2 and B1 (2 hours 21 minutes) then dividing by 2, which is 1 hour 10 minutes and 30 seconds [Ignoring for now the fact that all of these times are actually stored as fraction of a day values]. When the spreadsheet is then told to display only the hour+minute portion of this time, the seconds information is not displayed, leaving the display to be 1 hour 10 minutes. Before we talk about correcting B6, we need you to be very clear exactly what the correct value should be in B6.

Seem like the OT will be double-pay. But there are 2 ways of rounding,

=B1+MROUND((B2-B1)*1.5,"00:15")

or

=MROUND(B1+(B2-B1)*1.5,"00:15")

You keep saying you are needing to round to the nearest quarter hour, but your latest example of rounding to 1 hour 8 minutes suggests something else to me. Maybe you really need to round to the nearest 1/8 hour (7 minutes 30 seconds), then round that again to nearest minute? So your round points around the clock are 0, 8, 15, 23, 30, 38, 45, 53, 60? This would be a double round:
MROUND(value,TIME(0,7,30)) nested inside of another MROUND() to round to the nearest minute MROUND(MROUND(...),TIME(0,1,0))

Is that what you are trying to do? If that's not what you are trying to do, help us understand exactly what the calculation needs to look like. Maybe work a complete problem out by hand, so we can really see what you need to do.

Providing more mock up desired results

1) 1:13/2 (or 73 minutes divided by 2) should be 36.5 minutes (36 minutes 30 seconds). Rounded to the nearest minute is correctly 37 minutes. How do you get 38 minutes as the correct result here?
2) Can you enter this in your spreadsheet for full testing? My guess is that this is due to floating point (or some other rounding error) where the result of the first MROUND is slightly smaller than 1:07:30, which MROUND() to the nearest minute will round down to 1:07. The rounding algorithm may need further development to handle floating point and similar errors. Maybe something as simple as adding 1 second to the inner MROUND() before rounding to the nearest minute.
3) Same as 2

Okay, I think I understand now. I think all three cases are floating point error (difficult to say for sure without seeing the examples in a spreadsheet). Did you try my suggestion to add a small amount to the inner MROUND()?

At this point, my best recommendation is to use the Evaluate formula tool (https://support.microsoft.com/en-us/...6-a70aa409b8a7 ). Remember that the values will represent fractions of a day, so you may need some way (a hand calculator maybe) to see the time value represented. From this tool, you should be able to see the full value input to the formula as well as the value at each stage of the calculation. Identify where the value looks different from what you expect to see, then figure out what needs to happen differently.

Originally Posted by Dexter2

Problems 1 and 3 seem its just the floating error that was mentioned prior.

There are oh-so many calculations that could be improved to avoid binary arithmetic anomalies.

But at a minimum, the formula in D20, H20 and L20 should be of the form (in D20):

=MROUND(TEXT(MROUND(SUM(SUM(D16:D17)/24,MROUND((B2-TIME(11,0,0))*0.5,TIME(0,1,0))),TIME(0,7,30)),"[h]:m:s"),TIME(0,1,0))

In general, I use --TEXT(expression,"[h]:m:s") to round to the correct binary approximation of time that should be accurate to the second.

Likewise, I use --TEXT(expression,"[h]:m") to convert to the correct binary approximation of time that should be accurate to the minute.

(The use of "[h]" assumes that time is less than 9999:59:59.500.)

But as has been noted (I think), when Excel converts time to text of the form h:m, time is rounded to the second, then truncated to the minute.

In order to round to the minute, we might write --TEXT(expression+"0:30.0","[h]:m").

Excel interprets strings of the form "mm:ss.fff" as "m" minutes, "s" seconds and "fff" milliseconds.

The double minus ("--") or any other arithmetic operation converts text to a numeric value. But MROUND accepts text as input. So we can dispense with the explicit conversion.

-----

Originally Posted by Dexter2

However, problem 2, has me second guessing am I missing a calculation or have I calculated the wrong values, if at all.

Your expectation of 11:30 is misplaced. 11:38 is the correct answer, based on the suggestion to round to a multiple of 7m 30s, then round to the minute.

(Arguably, perhaps that suggestion was wrong for your purposes. I don't know. I have not followed the discussion in this thread in order to understand your intentions.)

The expression SUM(SUM(H16:H17)/24,MROUND((F2-TIME(11,0,0))*0.5,TIME(0,1,0))) results in 11h 34m.

And 11h 34m is closer to 11h 37m 30s than 11h 30m, the two nearest multiples of 7m 30s.

Originally Posted by Dexter2

but have not been able to combine or restructure to fit with the original

Please Login or Register  to view this content.

Or, if moving away from the original then, to tweak to retrieve the other answers for Problems 1 and 3.

The primary problem is: you round overtime/2 to the minute, then add "total time" (rounded to the 15-min) before rounding to the 7.5-min.

But yours and MrShorty's earlier comments suggest that first, you want to round overtime/2 to the 7.5-min, then round to the min before adding "total time", which is rounded to the 15-min.

Here, "total time" is according to my definition (e.g. SUM(D16:D17)/24), not your definition for D20.

To that end, in the attachment to your posting #17, the formula in D20 should be (and copy into H20 and L20):

Please Login or Register  to view this content.

Likewise, in the attachment to your posting #1, the formulas should be:

Please Login or Register  to view this content.

Note that I use TEXT to convert to the correct binary approximation of time that should be accurate to the minute and to the second. MROUND is not reliable, due to binary arithmetic anomalies.

(I can demonstrate that, on request. But I suspect that it is TMI. I also suspect that MROUND does not round to the 7.5-min and 15-min reliably.)

MAX(0,...) ensures that (B2-TIME(11,0,0))*0.5 does not result in negative time, either due to binary arithmetic anomalies, or because B2 is less than 11h 0m.

TEXT(...+"0:30.0","[h]:m") rounds seconds to the minute, instead of truncating.

Excel interprets "0:30.0" as 0m 30s, due to the decimal point and decimal fraction (required).


-----
PS.... Because "total time" (again, my definition) is rounded to the 15-min, I think (too tired to be sure) the formulas can be simplified as follows:

Please Login or Register  to view this content.

Originally Posted by Dexter2

Can you or anyone [...] help me understand what the actual logic is?

Not I, beyond my comments below.

Only you can tell us "what the actual logic is" (should be). And you have changed that logic from your first two attachments, as demonstrated by your latest formulas.

I am not going to waste my time (and yours) on solving one problem, only to learn later that the real problem is different.

Originally Posted by Dexter2

Cells shaded in yellow, I'm not sure why but I added + TIME(0,30,0) to reach the correct answer of 7.75 not 7.50.

I don't know. Again, only you can explain the __logic__. The calculations should follow.

The ST calculation has changed significantly from your first two attachments. And you never explained it, in the first place!

I invested significant time trying to understand the (manual) calculations in the first two attachments. I am not going to spend any more time trying to learn new (manual) calculations.

It is __your__ responsibility to explain the logic behind the calculations. That is, how you do them __manually__, not how you kludge the computer calculations in order to replicate the manual results.

Originally Posted by Dexter2

I tried to restructure the formula to utilize your (curiouscat) D20 formula from the previous, [...] but it screwed the rest of the problems up.
[....]
Can you or anyone assist me with creating a consistent formula for rows 3 and 15

The only reason why my formula "screwed up" is because you significantly altered the logic for the case when Day Span is less than 11h0m, and you made no effort to incorporate that change into the "D20" formula. If you were up-front about your inability to do that and called attention to it, I would be sympathetic. But I do not like it when people misrepresent facts in a way that impugns my efforts to help them.

That said, see my changes in the rows 3 and 20 to 23 of the "curiouscat" worksheet in the attached file.

I believe the changes in row 3 faithfully mimick the altered logic that is demonstrated by your "dexter2" formulas.

I believe the FALSE comparisons in "curiouscat" row 20 are due to mistakes in "dexter2".

For example, the "dexter2" formulas incorporate your same fundamental error in "manual" calculations that I explained previously, namely: rounding the entire sum (ST+OT plus half-overtime) to the 7.5min multiple, then to the min instead of rounding only the half-overtime to the 7.5min multiple, then to the min, as MrShorty suggested and you agreed to (postings #7 and 9).

That has nothing to do with any potential anomalies with binary or Excel-specific arithmetic.

(Errata.... I overlooked a subtle difference in the formulas when Day Span is less than 11h0m and Work exceeds 8h0m. That leads to corrections in the attachment and the following example.)

For example, I believe 8h23m is the correct result for M3 -- at least, as I understand your intended logic, perhaps incorrectly because you never explained it. To wit:

1. Paid overtime (M21) is 15 min = 8h15m - 8h0m
2. Paid half-overtime (M22) is 7.5 min = 15/2
3. Paid half-overtime rounded to the 7.5-min, then to the min (M23) is 8 min
4. ST+OT (E15+E16) plus rounded paid half-overtime (M3) is 8h23m = 8h15m + 0h8m

Errata.... I overlooked a subtle difference in the formulas when Day Span is less than 11h0m and Work exceeds 8h0m.

That leads to corrections in the attachment and my last example.

If you downloaded the attachment before this posting, please re-download and study the differences again.

Have you tried the evaluate formula tool to see what it is currently doing? Here's what I get when I step through the formula in C12:

1) Start with If condition of outer IF() function. C5+C9 is 7:33, which is greater than 7:22, so go to "value_if_false" argument.
2) Calculate If condition of value_if_false IF() function. C5+C9 is 7:33 which is less than 8:08, so go to "value_if_false" argument.
3) Calculate MROUND() function.
3a) C5+C9 is 7:33.
3b) 0:30:00*0.5 is 0:15:00.
3c) 7:33+0:15 is 7:48:00
3d) MROUND to the nearest 15 minutes is 7:45.
3e) Convert "fraction of a day" to decimal hours by multiplying by 24. 7:45 converted to decimal hour is 7.75. Result in C12 is 7.75.

In E12:
1) Start with If condition of outer IF() function. E5+E9 is 7:37, which is greater than 7:22, so go to "value_if_false" argument.
2) Calculate If condition of value_if_false IF() function. E5+E9 is 7:37 which is less than 8:08, so go to "value_if_false" argument.
3) Calculate MROUND() function.
3a) E5+E9 is 7:37.
3b) 0:30:00*0.5 is 0:15:00.
3c) 7:37+0:15 is 7:52:00
3d) MROUND to the nearest 15 minutes is 7:45.
3e) Convert "fraction of a day" to decimal hours by multiplying by 24. 7:45 converted to decimal hour is 7.75. Result in E12 is 7.75.

As programmed, the programmed formula is returning 7.75 as expected. I don't think we can help further without some explanation of the desired logic that should return 7.5

https://support.microsoft.com/en-us/...6-a70aa409b8a7