"MROUND" - Need Min. 0.25 Value

jcarey · Aug 15, 2017

I am utilizing "MROUND" to round to values of 0.25, 0.50, 0.75 & 1.00 - it works great, but I can't use a "0.00" value (I need min. 0.25) -- what can I do?

dreid1011 · Aug 15, 2017

Perhaps something like this:

=MAX(0.25,MROUND(A1,0.25))

MrKowz · Aug 15, 2017

Something like this?

Excel 2013/2016

A

B

C

1

Value

Mround Result

Min 0.25 Result

2

3.06028

3

3.25

3

6.871777

6.75

4

7.759659

7.75

5

3.54384

3.5

6

8.672333

8.75

7

7.34081

7.25

8

4.940666

5

5.25

9

9.664757

9.75

10

1.269969

1.25

11

6.875821

7

7.25

12

3.840254

3.75

13

2.429647

2.5

14

8.051095

8

8.25

15

3.753301

3.75

16

6.324389

6.25

17

10.0269

10

10.25

2

Cell Formulas
Range	Formula
B2	=MROUND(A2,0.25)
C2	=IF(MROUND(A2,0.25)=ROUND(A2,0),ROUND(A2,0)+0.25,MROUND(A2,0.25))

jcarey · Aug 15, 2017

MrKowz said:
Something like this?

Excel 2013/2016

A B C
Value Mround Result Min 0.25 Result

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3.06028[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3.25[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6.871777[/TD]
[TD="align: right"]6.75[/TD]
[TD="align: right"]6.75[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7.759659[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]7.75[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3.54384[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]3.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8.672333[/TD]
[TD="align: right"]8.75[/TD]
[TD="align: right"]8.75[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7.34081[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]7.25[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4.940666[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5.25[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9.664757[/TD]
[TD="align: right"]9.75[/TD]
[TD="align: right"]9.75[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1.269969[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.25[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6.875821[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7.25[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3.840254[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]3.75[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2.429647[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]8.051095[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8.25[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3.753301[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]3.75[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6.324389[/TD]
[TD="align: right"]6.25[/TD]
[TD="align: right"]6.25[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]10.0269[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10.25[/TD]

</tbody>
2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MROUND(A2,0.25)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(MROUND(A2,0.25)=ROUND(A2,0),ROUND(A2,0)+0.25,MROUND(A2,0.25))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I will try this...

jcarey · Aug 15, 2017

MrKowz said:
Something like this?

Excel 2013/2016

A B C
Value Mround Result Min 0.25 Result

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3.06028[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3.25[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6.871777[/TD]
[TD="align: right"]6.75[/TD]
[TD="align: right"]6.75[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7.759659[/TD]
[TD="align: right"]7.75[/TD]
[TD="align: right"]7.75[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3.54384[/TD]
[TD="align: right"]3.5[/TD]
[TD="align: right"]3.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8.672333[/TD]
[TD="align: right"]8.75[/TD]
[TD="align: right"]8.75[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7.34081[/TD]
[TD="align: right"]7.25[/TD]
[TD="align: right"]7.25[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4.940666[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5.25[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9.664757[/TD]
[TD="align: right"]9.75[/TD]
[TD="align: right"]9.75[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1.269969[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]1.25[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6.875821[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7.25[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3.840254[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]3.75[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2.429647[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]8.051095[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8.25[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3.753301[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]3.75[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6.324389[/TD]
[TD="align: right"]6.25[/TD]
[TD="align: right"]6.25[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]10.0269[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10.25[/TD]

</tbody>
2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MROUND(A2,0.25)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(MROUND(A2,0.25)=ROUND(A2,0),ROUND(A2,0)+0.25,MROUND(A2,0.25))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Having trouble.
I don't know if it's because the cell I'm trying to round is pasted as a link from another worksheet or not.
Here's my formula...

=MROUND('C:\Users\jcarey\Desktop\Temp - Time Sheet\[00) 00.00.0000 - 00.00.0000_Hour Tracker.xlsx]Sheet1'!$Y$8,0.25)

What might I be doing wrong?

MrKowz · Aug 15, 2017

Are you receiving an error? Is the formula returning any value? If so, what?

jcarey · Aug 15, 2017

It gave me a warning of circular formula at first, but then I hit "Okay" and I still got a value of "0".

Joe4 · Aug 15, 2017

If you are getting a circular formula error, it probably means that you mistyped the cell address in your formula, and you used the cell address of the cell that you are placing the formula in!
The formula should be reference cells where data exists (not where you are putting this formula).

jcarey · Aug 15, 2017

Joe4 said:
If you are getting a circular formula error, it probably means that you mistyped the cell address in your formula, and you used the cell address of the cell that you are placing the formula in!
The formula should be reference cells where data exists (not where you are putting this formula).

I fixed referencing the cells of the original file.
Now I get "0.25" even if original cell value is "0".

Joe4 · Aug 15, 2017

Now I get "0.25" even if original cell value is "0".

Isn't that what you asked for originally?

but I can't use a "0.00" value (I need min. 0.25) -- what can I do?

I am guessing that you have not communicated exactly what you want very clearly.
You may do better to provide us with a bunch of examples, and the results you would like to see.

"MROUND" - Need Min. 0.25 Value

jcarey

New Member

Excel Facts

dreid1011

Well-known Member

MrKowz

Well-known Member

jcarey

New Member

jcarey

New Member

MrKowz

Well-known Member

jcarey

New Member

Joe4

MrExcel MVP, Junior Admin

jcarey

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

"MROUND" - Need Min. 0.25 Value

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

Well-known Member

New Member

MrExcel MVP, Junior Admin

New Member

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock