Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Find missing values

    Out of curiosity, over the last 4 years I have kept track of which songs were sung out of our hymnal at church. I have been asked to give some facts about our hymnal and I would like to find out which songs have not been sung. I am totally lost on how to get that figure. I would also like to know which song is sung the most, and perhaps the top 10 or 20 that are sung. The numbers in the spreadsheet are the numbers of the song in the hymnal. I don't have the hymnal with me right now, but I think there are around 660 songs. Can someone help me?
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Find missing values

    Your spreadsheet would be better if you included a Unique values lookup table for Song Names and Numbers.

    From your data I firstly typed a list of all 660 song numbers, I then created a counting formula to find the song number in your listing and count how many times it appeared. I then determined what count was the highest number, and created a formula to show all those rows that matched. I then did the same with any zero's to find which songs had not been sung. To get the Top 10 you can use the filter tools on column D to get this.

    See attached updated file.

    Regards,
    Maria
    Attached Files Attached Files
    Maria
    Simmo7
    Victoria, Australia

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you. That's what I needed.

    <Your spreadsheet would be better if you included a Unique values lookup table for Song Names and Numbers.> OK. I'm not 100% sure I understood this part. In other words, I should have the actual song name (in text) in another column? Or, is Column C the 'Unique Value' you were mentioning?

  4. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by 79schultz View Post
    Thank you. That's what I needed.

    <Your spreadsheet would be better if you included a Unique values lookup table for Song Names and Numbers.> OK. I'm not 100% sure I understood this part. In other words, I should have the actual song name (in text) in another column? Or, is Column C the 'Unique Value' you were mentioning?
    You are welcome - glad I could help. What I meant about the unique values, was that if you had instead of my Column C a full list of song numbers in Column C and Names in Column D then once you have determined which song numbers were sung most or not at all, then you could easily lookup the name of the song.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Understood. Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •