Hey guys, I'm working on an NBA spreadsheet involving some various index-match formulas where a reference is being made for a team from the main page to match up with a date (y-axis - current date is always column E) and team (x-axis - varies based on the team the player is on).
Basically what I am trying to do is match a player's team row with the current date's NBA schedule (shown in bold and will always be in Column E), and then count how many days a player did not have a game before the one they currently have scheduled. In other words, how many days of rest (or days off) a player had prior to today.
So what I want to do is count the amount of blanks to the left a team's row has before the next non-blank cell occurs.
Here is the schedule tab, and what I'd like to do is use the index-match formula which I have here in my data tab to count the amount of blanks on the respective value tab before a non-blank value occurs on its left.
For example, the row for "(atl)" in my example, would register "1" blank before the next non-blank, while the row for "(cha)" would register a "0" because the team had played the day prior. Really all I'd like to do is embed a "count how many blank cells before the previous non-blank cell occured" formula into the data formula I've screenshotted: =IFERROR(INDEX(Schedule!$E:$E,MATCH(RIGHT(D3,5),Schedule!A:A,0)),"")
Hopefully I've given enough explanation and that one of you may have a solution for me, as I've spent hours googling and looking for similar examples without any luck! I really appreciate it, Mr. Excel!
Basically what I am trying to do is match a player's team row with the current date's NBA schedule (shown in bold and will always be in Column E), and then count how many days a player did not have a game before the one they currently have scheduled. In other words, how many days of rest (or days off) a player had prior to today.
So what I want to do is count the amount of blanks to the left a team's row has before the next non-blank cell occurs.
Here is the schedule tab, and what I'd like to do is use the index-match formula which I have here in my data tab to count the amount of blanks on the respective value tab before a non-blank value occurs on its left.
For example, the row for "(atl)" in my example, would register "1" blank before the next non-blank, while the row for "(cha)" would register a "0" because the team had played the day prior. Really all I'd like to do is embed a "count how many blank cells before the previous non-blank cell occured" formula into the data formula I've screenshotted: =IFERROR(INDEX(Schedule!$E:$E,MATCH(RIGHT(D3,5),Schedule!A:A,0)),"")
Hopefully I've given enough explanation and that one of you may have a solution for me, as I've spent hours googling and looking for similar examples without any luck! I really appreciate it, Mr. Excel!
Last edited:
