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

Lookup and Reference Examples

  • Perform case-sensitive Lookup in Excel
  • How to reference named range different sheet in Excel
  • How to get address of named range in Excel
  • How to use Excel INDIRECT Function
  • Lookup entire row in Excel

Data Analysis Examples

  • How to combine 2 or more chart types in a single chart in Excel
  • Understanding Anova in Excel
  • Understanding Pivot Tables in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How to count table columns in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation date in next 30 days
  • Excel Data validation exists in list
  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total

Excel Data validation whole percentage only

by

Set criteria to allow whole percentage only without decimals.

To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the TRUNC function.

Formula

=TRUNC(A1*100)=(A1*100)
Notes: Data validation rules are triggered when a user adds or changes a cell value. 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 B5.

Explanation

In the example shown, the data validation applied to B5:B9 is:

Worked Example:   Conditional formatting gantt chart in Excel
=TRUNC(C5*100)=(C5*100)

How this formula works

The Excel TRUNC function does no rounding, it just returns a truncated number. It has an optional second argument (num_digits) to specify precision. When num_digits is not provided, it defaults to zero. In this formula for data validation to allow we use TRUNC get the non-decimal part of a percentage, after we multiply the percentage by 100.

Worked Example:   AND function: Description, Usage, Syntax, Examples and Explanation

For example, if a user inputs 15%:

=TRUNC(.15*100)=(.15*100)
=TRUNC(15)=(15)
=15=15
=TRUE

If a user enters 15.5%, the formula evaluates like this

=TRUNC(.155*100)=(.155*100)
=TRUNC(15.5)=(15.5)
=15=15.5
=FALSE

This formula doesn’t validate anything else, for example that percentages are less than 100%. Additional conditions can be added with the AND function.

Worked Example:   Highlight dates between in Excel

Post navigation

Previous Post:

Excel Data validation unique values only

Next Post:

Excel Rank if formula Example

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 last characters from right in a cell in Excel
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • How to extract substring in Excel
  • Put names into proper case — Manipulating NAMES in Excel
  • SUBSTITUTE function vs REPLACE function in Excel
  • Add workdays to date custom weekends in Excel
  • Count dates in current month in Excel
  • Calculate series of dates by workdays in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • How to calculate Net Present Value (NPV) in Excel
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles