Restrict decimal entries

[Johnmus]

Hi all,

I would like to restrict decimal entries in a spreadsheet to be whole or half. For example 5.5, 7.5, 9.5, 22.5 is ok, but 5.1, 7.7, 9.4 ,22.6 etc. is not.

Is that possible with formula? The ranges of cells would be D4:D81,F4:F81, I4:I81, L4:L81

Thank you!​

 

[jtakw]

Hi,

Your description says you wanted to "restrict decimal entries in a spreadsheet to be whole or half", so what you need is Data Validation.

Select "Data" tab, select your cells, D4:D81, click "Data Validation", select "Data Validation", choose "Custom", enter this formula:

=MROUND(D4,0.5)=D4

Click "OK"

Repeat for F4:F81, changing formula to =MROUND(F4,0.5)=F4
Repeat for your other ranges, changing formula to reference the first cell.

This will restrict any numeric entry that's Not whole or half.

If this isn't what you want, please explain in detail what you need.

 

[fhqwgads]

for a formula you can use MROUND()

data	rounded
1.11	1
1.22	1
1.33	1.5
1.44	1.5
1.55	1.5
1.66	1.5
1.77	2
1.88	2
1.99	2
2.1	2

formula in the rounded column is

=MROUND(A2,0.5)

not sure how to do it using cell format which is probably closer to what you want though

 

[Johnmus]

Thank you for your quick response. Your suggestion to use MROUND works fine. Appreciate your help!

John

 

[jtakw]

You're welcome.