×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Data validation - not a multiple 0.05

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 ?

RE: Data validation - not a multiple 0.05

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: Eng-Tips.com Forum Policies

RE: Data validation - not a multiple 0.05

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/
 

RE: Data validation - not a multiple 0.05

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/
 

RE: Data validation - not a multiple 0.05

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?

RE: Data validation - not a multiple 0.05

(OP)
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 ?

RE: Data validation - not a multiple 0.05

(OP)
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.

RE: Data validation - not a multiple 0.05

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.

RE: Data validation - not a multiple 0.05

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

RE: Data validation - not a multiple 0.05

(OP)
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.
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources