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.
File Downloads Fixed
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)
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)
The Excel 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.
____________
I’ve never used either FLOOR or SIGN functions before so I’m really glad to see them in an example that makes perfect sense. I can’t wait to try this out on some of my data.
And I’m not a big Neil Young fan, but that clip was AWESOME. Fantastic post!
@Deb: Is there any formala(Inbuilt) where in I can roundoff the number based on it’s proximity to any significance? For Ex: It’s should round off number 1984 to 1980 and 1987 to 1990, if the significance is 10. To put this in excel language =formula(number,significance) so =formula(1984,10) should give 1980 on the other hand =formula(1987,10) should give 1990.
Thank You
@Prakash Singh Gusain The MRound function should do that.
@Debra: Thanks a lot and Merry Christmas! got your reference from Chandoo.org. I must admit, another gem discovered by me. 🙂