Data validation - not a multiple 0.05

[DiscoP]

Hello

I produce spreadsheets that determine testing points based on some given parameters.

For one particular device, there is a parameter that is rounded to the nearest 0.05 - the setting software used allows it to be entered to the nearest 0.01 - and it is rounded internally.

So most of the time the values are not rounded in the proposed settings, and this creates a minor error, (which often doesn't matter too much, but sometimes causes confusion).

However I was wondering if there was a way to put up a warning on the spreadsheet if the figure is not a multiple of 0.05

Does anyone know a way ?

 

[IRstuff]

Why wouldn't flagging remainders from mod(x,0.05) work?

But, why not just fix the rounding routine, or round it in the spreadsheet?

TTFN
faq731-376

 

[IDS]

In an adjacent cell:
=IF(A1/0.05-INT(A1/0.05)<>0), "Value is not a multiple of 0.05!", "")

Adjust the address A1 as required.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

IRStuff - MOD() seems to give false positives, e.g.

=MOD(1,0.05) = 0.05

But yes, rounding the values to 2 decimal places would probably be a better way.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[AELLC]

Instead of warning that the input is not a whole multiple of 0.05, why not allow any value and then have the Excel round it to the proper value internally?

 

[DiscoP]

Thanks - that was the key !

I've tried to get the rounding routine fixed, but there are too many people in our organisation that can create these parameters, and all doing them different ways (most not using spreadsheets). I'm a small cog in a big machine !

The setting software should do the correction, the manufacturer keeps saying they will fix it, but it never gets done.

This could be a silly question.

Was going to do the =MOD(Cell,0.05), and flag when the result is not equal to 0

However with larger numbers (say 12), the result is a small number eg -6.66134E-16

Rounding the result to 2 decimal places seems to fix it ie =IF(ROUND(MOD(C14,0.05),2)<>0,"Error","OK")

Is there a simpler way ?

 

[DiscoP]

AELLC , that is what I have been doing, but the paperwork never gets tidied up.

A validation and flag of the data up front ensures everything is correctly sorted out.

IDS - thanks that seems to work too. Note that you had an extra ) in your equation.

 

[RyreInc]

In Excel 2007 under the Data tab there is something called Data Validation, which will allow you to restrict inputs to a cell. Unfortunately, there is no native option to round to arbitrary resolution, but perhaps you could provide a numbers list or use the custom option?

Using this method will provide a popup warning when invalid numbers are entered.

 

[ierland]

In stead of using additional cells did you contemplate conditional formatting?

Data Validation would probably not help in your case. It is nice for data as manual input. If you are copying/pasting data you just overwrites the existing cell characteristics.

For rounding use MROUND rather than unnecessary tricks with MOD.

Why an easy solution if you can make it complicated?
Greetings from the Netherlands

 

[DiscoP]

Hi. In the end I went with the suggestion from IDS - yes I used this formula as conditional formatting for each cell. Thanks to everyone for their suggestions.