Allowing restricted decimal values in a cell

[srilam]

Dear,

I have a requirement to allow only .5 for any number. say for eg; in cell A1, i can allow 12.5 or 6.5 and not other decimals like 12.4 or 12.8 etc. Need to restrict the end user with a alert message also.

I tried with making the cell as decimal but couldnot restrict the user in entering beyond .5

is there any formula and not VBA code possible to restrict and alert user.

Thanks, Srikanth

 

[vlady]

Go to Data Validation under "Data" -> Choose Custom then enter in the formula

=A1-INT(A1)=0.5


change your messages (input message and error alerts)

 

[Michael M]

Why not use Data Validation
select Custom from the Allow list and input this formula

=MOD(A1,0.5)=0

You can then add entry and alert messages to suit via the Data validation Options

 

[JoeMo]

You could use data validation with this formula:

=AND(ISNUMBER(A1),MOD(A1,1)=0.5)

but be aware that a user can paste any data to A1 and destroy the validation. VBA could provide a more robust solution, but I understand you don't want a VBA solution.

 

[srilam]

Hey All

Thanks for your quick replies. it worked. thanks for your support

Joe, I understand, currently we have some technical issues on vba plugins that is why this turnaround

Srikanth