Fix the FLOOR to Round Down in Excel

image Earlier this week, you read about the Top 100 Canadian Singles, and saw the pivot table that summarized the top songs by decade. In the comments, Martin mentioned the FLOOR function, that I used to calculate each song’s decade, based on its release year.
Martin also pointed out that the files weren’t downloading, and I finally managed to fix that — sorry about the inconvenience. Take my advice, and don’t work on your blog while travelling, if you can avoid it! Things that work perfectly at home, refuse to cooperate when you’re on the road.

FLOOR It

The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify. In the Canadian Music file, the decade is being calculated, so 10 is used as the multiple.
=FLOOR(A2,10)
FloorFunction01
In column B, you can see the result of the FLOOR function, rounding down the year for each song, to show the song’s decade.

Trouble on the FLOOR

In the FLOOR function, if the number and multiple have different signs, the result is the #NUM! error. The FLOOR function works well in the music example, because the song’s year is always a positive number.
If you’re working with a list that contains both positive and negative numbers, you could use the SIGN function to calculate the number’s sign, and change the multiple to match it.
=FLOOR(A2,SIGN(A2)*10)
FloorFunction02
The SIGN function result is 1 for positive number, -1 for negative numbers, and 0 for zero.

Heart of Gold

And finally, for your Friday listening pleasure, here is the second song on the Top 100 Canadian Singles list — Neil Young playing Heart of Gold in 1971.

____________