Happy Friday Excellers and welcome to another #formulafriday blog post. I found this little Excel formula out by accident. I was trying to find out what decade a date was from and thought it may have its uses along the way in Excel.
Whilst most numbers will let you round up or down to a specific decimal place, the FLOOR function will allow you to specify yourself what number you want to round down to. Basically, this function will round a number down to zero, to the nearest multiple of significance which you specify.
If you want to download the workbook for the #formulafriday 2019 Season then click on the link below.
[ddownload id=”14616″]
FLOOR Function Syntax
=FLOOR(number, significance)
where
number – This is required. The numeric value you want to round.
significance – Also required. The multiple to which you want to round.
Using FLOOR With An Example
So, in the example below, the release date (year) of the song is the NUMBER part of the function and the SIGNIFICANCE is 10 which is a decade. So the release date of the song is rounded down to the decade.
You could change the SIGNIFICANCE to 100 and this would give the CENTURY!. Give this a go. Why not?.
Notes To Note!
- When either argument is non-numeric, FLOOR returns the #VALUE! error value.
- If a number is positive and significance is negative, FLOOR will return the #NUM! error value.
- If the sign of the number is positive, a value is rounded down and adjusted toward zero. Alternatively, if the sign of the number is negative, a value is rounded down and adjusted away from zero. If the number is an exact multiple of significance, no rounding occurs.
So, What Next? Want More Tips?
If you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.
