Happy Friday Excellers…I found this little Function out by accident, looking to see what decade a date was from, and thought it may have it’s 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.
FLOOR Function Syntax
number – Required. The numeric value you want to round.
significance – Required. The multiple to which you want to round.
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.
- If either argument is non numeric, FLOOR returns the #VALUE! error value.
- If number is positive and significance is negative, FLOOR will return the #NUM! error value.
If the sign of number is positive, a value is rounded down and adjusted toward zero. If the sign of number is negative, a value is rounded down and adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
Do you want to learn more formulas, in a fast and fun way?
75 of the most frequently used Excel formulas explained with examples. Get your GO FASTER STRIPES with this fantastic E- Book.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Formula Friday Series you can do so by clicking on the link below.