Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows with at least n matching values
  • Extract data with helper column in Excel
  • Count unique text values with criteria
  • How to use Excel FORMULATEXT function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • How to create running total in an Excel Table
  • Conditional Formatting Color Scales Examples in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Filter Data Based on Date in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation whole percentage only
  • Excel Data validation allow uppercase only
  • Excel Data validation date in specific year
  • Excel Data validation with conditional list

Excel Data validation number multiple 100

by

Accept only numbers that are multiple of 100

To allow only numbers in multiples of 100, you can use data validation with a custom formula based on the MOD function.

Formula

=MOD(A1,100)=0

Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.

Worked Example:   Extract time from a date and time in Excel

Explanation

In the example shown, the data validation applied to C5:C10 is:

=MOD(C5,100)=0

How this formula works

Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When the formula returns FALSE, validation fails and the input is rejected.

Worked Example:   Convert inches to feet and inches in Excel

In this case, the MOD function is used to perform a modulo operation, which returns the remainder after division. The formula used to validate input is:

=MOD(C5,100)=0

The value in C5 is 500. The MOD function divides 500 by 100 and gets 5, with a remainder of zero. Since 0 = 0,  The rule returns TRUE and the data validation passes:

=MOD(500,100)=0
=0=0
=TRUE

If a user enters, say, 550, the remainder is 50, and validation fails:

=MOD(C5,100)=0
=MOD(550,100)=0
=50=0
=FALSE
Worked Example:   Calculate time difference in hours as decimal value in Excel

Post navigation

Previous Post:

Excel Data validation no punctuation

Next Post:

Excel Data validation only dates between

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove first character in a cell in Excel
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • How to get last word in a cell in Excel
  • How to create email address from name in Excel
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • How to calculate Quarter of Date in Excel
  • Get days between dates in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Age in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • Example of payment for annuity in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles