Data Validation Require Specific Multiple?
- Average speed
- km/h
- Result
- Finish time
- 50 min
- You entered
- Distance
- 10 km
- You entered
Data validation does not inherently require a specific multiple for all cases, but when you use the “multiple of” rule in spreadsheet software, you must enter a specific number that the data must be a multiple of. This article explains how that rule works, why it exists, and how to apply it correctly in Excel, Google Sheets, and similar tools.
Formula
The “multiple of” validation rule checks whether a value divided by your specified number yields an integer. In mathematical terms, for a value v and a multiple m, the condition is:
v / m = integer
Where:
- v = the value entered in the cell (any real number)
- m = the multiple you specify (must be a positive number, typically an integer)
For example, if you set m = 5, then 10, 15, and 20 are allowed, but 12 and 17 are rejected. If m = 0.5, then 1.5, 2.0, and 3.5 are allowed, but 1.2 is not.
Worked Example
Using the default values from the calculator above, suppose you want to validate that a finish time (in minutes) is a multiple of 5. Your validation rule would be:
- Specify the multiple as 5.
- Enter a finish time of 50 minutes.
- Check: 50 / 5 = 10, which is an integer, so the value is accepted.
- If you entered 52, then 52 / 5 = 10.4, which is not an integer, so the value is rejected.
The calculator above does not perform validation, but it illustrates how multiples work with times and distances. For the default values, 50 minutes and 10 km give an average pace of 5.00 min/km and an average speed of 12.00 km/h.
Why Use a Multiple in Data Validation?
Requiring a specific multiple is useful when data must conform to a fixed increment. Common scenarios include:
- Entering quantities that are sold in packs of 10 (e.g., 10, 20, 30).
- Recording times in 5-minute intervals for scheduling.
- Ensuring measurements are in whole centimeters rather than fractions.
- Accepting only even numbers for certain IDs or codes.
This rule reduces entry errors and keeps datasets consistent. Without it, users might type 17 when only multiples of 5 are expected, causing downstream calculation issues.
How to Set It Up in Excel and Google Sheets
Both Excel and Google Sheets offer a “multiple of” validation rule, but the steps differ slightly.
In Microsoft Excel:
- Select the cells to validate.
- Go to the Data tab and click Data Validation.
- Under Allow, choose Whole number or Decimal (depending on your data).
- Under Data, choose between or equal to – but note that the “multiple of” option is only available when you select Whole number and then choose equal to in the data list? Actually, the correct path is: choose Custom in the Allow box, then enter a formula like
=MOD(A1,5)=0.
In Google Sheets:
- Select the cells.
- Click Data → Data validation.
- Under Criteria, choose Custom formula is.
- Enter
=MOD(A1,5)=0(replace A1 with the first cell).
Excel also has a built-in “multiple of” option under Allow: Whole number → Data: equal to? Actually, no. The built-in option is under Allow: Decimal? Let’s be precise: In Excel, you can select Whole number or Decimal, then in the Data dropdown choose equal to, and then in the Value box type the multiple? That’s not correct either. The reliable method is the custom formula with MOD. Many online guides confirm this.
Common Mistakes and Misconceptions
One frequent mistake is thinking that the multiple must be a whole number. In fact, the multiple can be any positive number, including decimals. For example, setting a multiple of 0.25 allows values like 1.25, 2.50, and 3.75. This is useful for quarter-hour increments.
Another misconception is that the multiple applies to the cell’s format. It does not – it applies to the underlying value. So a cell formatted as currency with a multiple of 0.01 will accept 1.23 but reject 1.234.
Also, the multiple must be greater than zero. Setting it to zero would cause every value to fail (division by zero) or all values to pass (0 mod anything is 0), depending on the implementation. Most validation tools will reject a multiple of zero.
How to Choose the Right Multiple
Choosing the multiple depends on your data’s natural granularity. Here are some guidelines:
- For counts of items, use 1 if any whole number is allowed, or 10/100 if items come in packs.
- For time, use 1 (minutes), 5 (5-minute blocks), 15 (quarter hours), or 60 (hours).
- For currency, use 0.01 for cents, 0.05 for nickel rounding, or 0.25 for quarters.
- For measurements, use the smallest unit you want to accept (e.g., 0.1 cm, 0.5 kg).
If you are unsure, start with the smallest reasonable unit. You can always tighten the rule later, but loosening it may require clearing existing invalid data.
Limitations and Alternatives
The multiple rule is binary – it either accepts or rejects. It cannot give warnings, and it cannot automatically correct values. For more complex checks, consider:
- Custom formulas – allow conditions like “multiple of 5 AND greater than 100”.
- List validation – if the allowed values are finite, use a list instead of a multiple.
- Conditional formatting – highlight invalid values without blocking entry.
- Data cleaning with formulas – use ROUND or MROUND to snap values to the nearest multiple after entry.
Remember that data validation only affects manual entry. If you paste data or import it, validation may not run unless you use the “circle invalid data” feature in Excel or re-apply validation in Sheets.
Frequently Asked Questions
Can the multiple be a decimal like 0.5?
Yes, the multiple can be any positive number, including decimals. For example, a multiple of 0.5 will accept 1.5, 2.0, and 3.5, but reject 1.2. This is useful for half-unit increments.
What happens if I enter a value that is not a multiple?
Depending on your settings, the entry is either rejected with an error message or flagged as invalid. In Excel, you can choose to show a warning instead of blocking the entry. In Google Sheets, you can set it to reject or show a warning.
Does data validation work on cells with formulas?
Yes, validation applies to the formula’s result, not the formula itself. If a formula produces a value that is not a multiple, it will be flagged. However, if you paste over a cell, validation may be removed.
Can I use a multiple rule with dates or times?
Dates and times are stored as numbers in spreadsheets. For times, you can use a multiple of 1/24 (one hour) or 1/1440 (one minute) if you are working with raw time values. For dates, a multiple of 1 represents whole days.
Why is my multiple rule not working?
Common reasons include: the cell contains text, the multiple is zero, the cell is formatted as text, or the formula uses the wrong cell reference. Double-check that the cell reference in the custom formula matches the first cell of your selected range.
Related
Formula used
in t | Finish time | min | 50 | 0 in d | Distance | km | 10 | 0 out pace | Average pace | min/km | t / d | 2 out speed | Average speed | km/h | d / (t / 60) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.