mod(186,6) return 6, not 0

[borntobe]

Hi,
I used mod function to get remainder for 246.

=IF($M2<>0,IF($M2>60,$M2+IF(MOD($M2-60,6)>0,6,0),60),0)

I used above formula in column N.
Column M has formula too.

If M2 has bigger than 60, then minus 60, and then divide by 6.
IF remainder exist, then add 6, or add 0.

M2 was 246, and N2 got 252. But I expect 246.
What is wrong?

Thanks.

 

[Jonmo1]

I would expect that M2 does not = 246 'EXACTLY'
It could be 246.1 or some other decimal amount, and you have the cell formatted to show 0 decimals.

Format M2 to show 15 decimals, what do you see.

 

[borntobe]

I'm using 'minutes'.
Cell B2 has '00:04:06' (hh:mm:ss format). This means 4 minutes and 6 seconds.
I calculated 'seconds', so I multiplied 60 to cell B2, then got 246 seconds.

First, I have to minus 60 initially, then after minus, I have to divide by 6.
If I got remainder, then I consider that as 6, otherwise 0.

Please advise me.
Thanks.

 

[Jonmo1]

What does B2 have to do with it? Your formula uses M2...

Anyway..

TIME is a decimal value (less than 1).
1 = 1 day = 24 hours
0.5 = half a day = 12 hours
etc..

246 is a whole number, 246 seconds in reality = 0.0028472

To convert an actual time value to 'Number of seconds' in whole numbers, try multiplying it by 86400
60 seconds in 1 minute
60 minutes in 1 hour
24 hours in 1 day
60*60*24 = 86400

=IF($M2<>0,IF($M2*86400>60,$M2*86400+IF(MOD($M2*86400-60,6)>0,6,0),60),0)

 

[borntobe]

I am sorry.
Here is my sheet.
Col B ColL ColM col N
00:04:06 4.1 246 I expect 246, but result is 252, because of mod function returned 6, not 0.

Col B: has format hh:mm:ss <<== provided data
Col L : is minute of usage <<==provided data
ColM : I used formula to get seconds. I just simply multiplied 60, because I need 'seconds'.
ColN: I used mod function here.

Could you tell me how to use your formula in column M and N?
Thanks so much!

 

[Jonmo1]

Can you post the formulas you have in L2 and M2 ?

 

[Jonmo1]

Keep in mind that with TIME values, there is a fairly large 'variance' in the actual real value when formatted as a time showing seconds.
Having a time EXACTLY equal to a specific second is not likely

You may consider rounding your time value to the nearest second.
=MROUND(B2,"00:00:01")

 

[borntobe]

Col L2 doesn't have formula, because a manager gave this to me.
I added formulas in column M and column N.
Column M, I used '=L2*60'.
Column N, I used '=IF($M2<>0,IF($M2>60,$M2+IF(MOD($M2-60,6)>0,6,0),60),0)'

These are my original codes.
Thanks.

 

[borntobe]

What number format do I need for column M and N?

I think I better use column B, so I used your MROUND function in column L, then multiple 86400 in column M to get 'Seconds'.
I used 'MOD' function in column N, but I got the same results, 252.

I don't know what to do.
Thanks.

 

[Jonmo1]

ok, if M2 isn't actually a time value, you've already multiplied minutes by 60, then you don't need the 86400 any more.
But you should definitely be rounding M2.

Try
=IF($M2<>0,IF($M2>60,$M2+IF(MOD(MROUND($M2,"00:00:01")-60,6)>0,6,0),60),0)