Scribd
Upload
112 views3 pages

Excel Data Validation and Rounding Formulas

This document provides examples of different types of data validation that can be used in Excel, including validating for numbers only, text only, uppercase only, dates within a specific range, existing in a list, beginning or containing specific text, whole percentages only, and more. It also provides examples of rank and round functions for working with and manipulating numbers.

Uploaded by

B
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
112 views3 pages

Excel Data Validation and Rounding Formulas

This document provides examples of different types of data validation that can be used in Excel, including validating for numbers only, text only, uppercase only, dates within a specific range, existing in a list, beginning or containing specific text, whole percentages only, and more. It also provides examples of rank and round functions for working with and manipulating numbers.

Uploaded by

B
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Data validation

Data validation allow numbers only


ISNUMBER
Data validation allow text only
ISTEXT
Data validation allow uppercase only
UPPEREXACTAND
Data validation allow weekday only
YEARTODAY
Data validation date in next 30 days
ANDTODAY
Data validation date in specific year
YEARTODAY
Data validation don't exceed total
SUM
Data validation exists in list
COUNTIF
Data validation must begin with
EXACTLEFTCOUNTIF
Data validation must contain specific text
FINDISNUMBER
Data validation must not contain
ISNUMBERSEARCHSUMPRODUCT
Data validation must not exist in list
COUNTIF
Data validation no punctuation
FINDCOUNT
Data validation number multiple 100
MOD
Data validation only dates between
ANDDATE
Data validation require unique number
ANDISNUMBERCOUNTIF
Data validation specific characters only
MATCHCOUNTLENMIDINDIRECT
Data validation unique values only
COUNTIF
Data validation whole percentage only
TRUNCAND
Data validation with conditional list
IF

Rank
Rank function example
RANK
Rank if formula
COUNTIFSRANK
Rank race results
RANK
Rank with ordinal suffix
CHOOSEABSMOD
Rank without ties
RANKCOUNTIF

Round
Get decimal part of a number
TRUNC
Get integer part of a number
TRUNCINT
Get number at place value
MOD
Round a number
ROUND
Round a number down
ROUNDDOWN
Round a number down to nearest multiple
FLOOR
Round a number to n significant digits
ROUNDLOG10INTABS
Round a number to nearest multiple
MROUND
Round a number up
ROUNDUP
Round a number up to nearest multiple
CEILING
Round a number up to next half
CEILING
Round a price to end in .99
ROUND
Round by bundle size
CEILING
Round time to nearest 15 minutes
MROUNDCEILINGFLOOR
Round to nearest 1000
ROUND
Round to nearest 5
MROUNDCEILINGFLOOR

You might also like