Data validation - not a multiple 0.05
Data validation - not a multiple 0.05
(OP)
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 ?
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 ?





RE: Data validation - not a multiple 0.05
But, why not just fix the rounding routine, or round it in the spreadsheet?
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
RE: Data validation - not a multiple 0.05
=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/
RE: Data validation - not a multiple 0.05
=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/
RE: Data validation - not a multiple 0.05
RE: Data validation - not a multiple 0.05
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 ?
RE: Data validation - not a multiple 0.05
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.
RE: Data validation - not a multiple 0.05
Using this method will provide a popup warning when invalid numbers are entered.
RE: Data validation - not a multiple 0.05
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
RE: Data validation - not a multiple 0.05