Concert set lists: finding the first and last time a song was played

[foamy2001]

https://docs.google.com/spreadsheets...it?usp=sharing

I'm trying to use excel to compile statistics about a specific music artist's concert set lists. I've been using COUNTIF for most things (how many shows were played on a certain day of the week, total number of times a song was played, etc.) but I can't figure out a formula to tell me the first and last time a song was played.

I don't know if I have the spreadsheet set up to be most effective, so I'm also looking for any help about how I might improve my formatting. Let me know if there is a problem with posting the link above.

[JBeaucaire]

Update your sample workbook to manually demonstrate the results you're looking for, make it easy for us to see what you want to automate?

[foamy2001]

Thank you for the quick reply.

I added a second sheet and labeled both. I want the sheet labeled "song list" to have column B show the date of the first performance of a song, with column C showing the date of the most recent performance of a song. I manually looked up the first song on the list and entered the data manually. Is there a formula that would do this work for me with the data arranged as is, or do I need to change the database (the sheet titled "set lists").

Thanks again.

[JBeaucaire]

Like so... had to add the missing dates on sheet1... lots of helper columns on sheet2, the results are where you wanted them, though.

No clue if Excel functions work in Google docs.

[foamy2001]

Thank you. Now it's just a matter of digging into the formula to figure out what's going on.

Was this the best way to organize the information or is there a better way?

Also, for future reference, I guess I should always upload an actual excel sheet?

Regardless, thank you for your help.

[foamy2001]

a couple quick questions:

I'm guessing it would be easier to make a column in the sheet titled "set lists" that has the date. Would this avoid using the DATEVALUE portion of the formula?

I'm having a hard time figuring out part of the "last time played" formulas: Why did you use LOOKUP instead of INDEX, MATCH? Also in the formula "LOOKUP(2, 1/(...) what does the "2, 1/" do?

Thanks again for your help. It's taking me a while, but I've been able to deconstruct what's going on in your formulas and have learned a lot. I've watched and read a bunch of tutorials, but most don't deal with the type of data I'm playing with.

[JBeaucaire]

The LOOKUP function is a fuzzy match function. If the data is ordered and you're searching for a specific value and the value is NOT in the list, the LOOKUP will return the last value it found in the list below the value you're searching for. Simple enough.

For example, if you had 2,4,6,8,10 listed in cells A1:A5, and you looked for 7 in those cells with:
=LOOKUP(7, A1:A5, A1:A5)
...it would return a 6 since that's the closest and last number in the list under the searched value. OK so far?

Well, LOOKUP is used as a "trick" to find the last value in a range as well. This construct is being used on your project too.

We are searching for the number 2 in a range of values that are all going to be less then 1, this is true because we are dividing all the answers found into 1. 1/(....) means every answer found will result in fractions of 1, like 1/5, 1/4, etc, and the rows that do no match the value test within the (...) will result in #DIV/0 errors. Those will be ignored by the LOOKUP.

So, the formula finds every row that matches, divides that row number by 1, creates an array of all those results and then looks for "2" in that resulting list. Since 2 won't be there anywhere, it will default to showing the LAST numeric value in the array it created, essentially returning the last instance of a match.