User profile for user: AndyK2015
AndyK2015 Author
User level: Level 1
4 points

My Table is Bugging and I Don't Know Why...

Hi all,


I posted on here a couple weeks ago about my giant hockey spreadsheet. Since then, I've learned a lot and went even further (thanks SGIII!).


Here is a quick rundown of the spreadsheet:


  • Sheets 2-6 are for data input. Dates on the x, Teams on the y. The probability for a team to get into a certain round of the playoffs is the meat of those tables. In each of those sheets, there is a bar chart and a line graph that graphically displays the data, as well as a table that ranks the top x teams for that round.
  • Sheet 7 is a playoff picture calculator. It will take each team's wins/losses, calculate the points each team has, and then organizes all of them in various ranking systems and playoff brackets based on the data in Sheets 2-6.
  • Sheet 1 is a 5-nested pie chart to display all of the data for a specific day at once (if anybody is familiar with MoneyPuck(dot)com, it is an emulation of their pie chart.


This sheet is where I'm running into issues. Here is how that sheet works:


  • Each round of the playoffs is on the x, each team is on the y (teams are in a different order solely for aesthetic reasons in the pie chart (division/conference splits)
  • I've successfully created a drop-down menu table that helps select the date. First row chooses the month, then from there the proper date-selection drop-down menu appears to select a date. Most cells are hidden in this since I want to only show the cells directly needed to choose a date. At the bottom of this table (also hidden), it translates the month into a number and grabs the actively shown date. As far as I can see, this table works perfectly.
  • The main table in this sheet concatenates these numbers in a MM/DD format in A1. This also works perfectly.
  • The body of the main table is supposed to find the intersection of the date (in A1) and the team name (A:A) in the sheet listed in (1:1) (hardcoded). This has worked perfectly, until today.


For some odd reason, this entire pie chart sheet works perfectly, has for a week now. It will work for any date before 3/1, but once that date (or any after it) are selected, the table shows all zeros.


Here's why I find this super weird:


I can tell the cells aren't getting an error, because I used IFERROR(...,""), so if the formula gave an error, cells would be blank. This was tested by putting gibberish in A1, where the date should be. This correctly returned blank cells throughout the table.


For SOME REASON, when March 1 or any date after that is input into A1, every cell returns a 0. When checked with previous dates before March 1, this works as expected.


All references have been checked and are correct. I even had my computer science colleague (I'm a math teacher), with a post-doc and has more experience with spreadsheets than I ever will, look at this, and he is dumbfounded as well.


I have included screenshots of what I can, but if there's a way for me to share the spreadsheet itself: it may be too complicated to explain over a text forum such as this.


Any help appreciated!



**Formulas in screenshots show B2 (Make Playoffs, MTL)


iMac 24″

Posted on Mar 1, 2024 1:38 PM

Reply
Question marked as Top-ranking reply
User profile for user: AndyK2015
AndyK2015 Author
User level: Level 1
4 points

Posted on Mar 2, 2024 3:30 AM

I gotta be honest with you, most of my coding is off the dome. I just think about what needs to be done, and my inexperienced butt tries to make sense of the functions. I tried using MATCH in place of COLUMN(LOOKUP()) but that also produced an error: MATCH could not find the value "3/1".


I think I see what you were saying with the LOOKUP function searching for similar items. I isolated the LOOKUP() function and it returned 3/19. In trying different combinations, all dates (MM/1, MM/2, MM/3) are returning MM/19, MM/29, MM/31 (or 30).


I tried using XLOOKUP() two ways and both gave me an error. I tried leaving the rest as is, with REGEX(), and the error is Argument 5 is invalid. Could that be because it's looking for an exact match, but the cells only match exactly to the human eye, not the program? I tried making the date in the Make Playoffs table just text, but that didn't do anything. I also tried eliminating the REGEX from the formula and kept it XLOOKUP($A$1. Make Playoffs::Table 1::$1$1, Make Playoffs::Table 1::$1$1, if-not-found, exact match). This tells me XLOOKUP couldn't find the requested value.


I FIGURED IT OUT!! I guess it was finding the closest match but ignoring the exact match that came before it (super weird), but HLOOKUP seemed to do the trick. I replaced the LOOKUP function with HLOOKUP(REGEX($A$1),Make Playoffs::Table 1::$1$1, 1, exact match). This seemed to do the trick.


Thanks so much for steering me in the right direction! This was KILLING me yesterday.

View in context

Similar questions

8 replies
Question marked as Top-ranking reply
User profile for user: AndyK2015
AndyK2015 Author
User level: Level 1
4 points

Mar 2, 2024 3:30 AM in response to Badunit

I gotta be honest with you, most of my coding is off the dome. I just think about what needs to be done, and my inexperienced butt tries to make sense of the functions. I tried using MATCH in place of COLUMN(LOOKUP()) but that also produced an error: MATCH could not find the value "3/1".


I think I see what you were saying with the LOOKUP function searching for similar items. I isolated the LOOKUP() function and it returned 3/19. In trying different combinations, all dates (MM/1, MM/2, MM/3) are returning MM/19, MM/29, MM/31 (or 30).


I tried using XLOOKUP() two ways and both gave me an error. I tried leaving the rest as is, with REGEX(), and the error is Argument 5 is invalid. Could that be because it's looking for an exact match, but the cells only match exactly to the human eye, not the program? I tried making the date in the Make Playoffs table just text, but that didn't do anything. I also tried eliminating the REGEX from the formula and kept it XLOOKUP($A$1. Make Playoffs::Table 1::$1$1, Make Playoffs::Table 1::$1$1, if-not-found, exact match). This tells me XLOOKUP couldn't find the requested value.


I FIGURED IT OUT!! I guess it was finding the closest match but ignoring the exact match that came before it (super weird), but HLOOKUP seemed to do the trick. I replaced the LOOKUP function with HLOOKUP(REGEX($A$1),Make Playoffs::Table 1::$1$1, 1, exact match). This seemed to do the trick.


Thanks so much for steering me in the right direction! This was KILLING me yesterday.

Reply
User profile for user: Badunit
Badunit
User level: Level 7
30,336 points

Mar 2, 2024 6:14 AM in response to AndyK2015

I think what you were seeing is from the direction of the search. If one function is searching left to right, it will find 3/1 before it finds 3/19. If the other is searching right to left it will find 3/19 before it finds 3/1. If there is no "exact match" it will return the first "close match" it finds (unless it is set to return "exact match" only). All the search functions should return an exact match if there is one, except if you are using REGEX (which just now dawned on me).


REGEX("3/1") matches 3/1, 3/11, 3/12, etc. It returns matches for any text that has "3/1" in it, including "ABC123/12345DEF". If the lookup function you are using searches row 1 left to right it will find 3/1 first. If it searches right to left it will find 3/19 first. LOOKUP documentation doesn't say what it does with REGEX, whether it tries to find the exact match or returns the first match but, regardless, you don't want REGEX in the formula.


Not that there is anything wrong with the HLOOKUP function, other than its age, but I would go back to trying XLOOKUP and make sure it works. Something is going on that is/was preventing an exact match.


You don't care about the year so I recommend A1 and all of Row 1 of the other table be text. But first format all of row 1 as date&time with the year showing. Verify that they all became dates. Then format them to not show the year then format them as text. That should make sure they are all text while also removing any weird or extra characters. If you need A1 as a date in a downstream formula, you can use DATEVALUE(A1).


This formula should get the correct answer if A1 and all of Row 1 are text:

=XLOOKUP($A$1, Make Playoffs::Table 1::$1$1, Make Playoffs::Table 1::$1$1, "not found", 0)


My guess is your table originally went to the end of February then you added March later. Something is different about March. You may have already fixed it but you should make sure.


Reply
User profile for user: Badunit
Badunit
User level: Level 7
30,336 points

Mar 1, 2024 5:20 PM in response to AndyK2015

I'm not yet seeing the problem then. How about we tear into the formula in this cell. Remove everything except the lookup that uses A1

=LOOKUP(REGEX($A$1),Makes Playoffs Table::$1:$1,Makes Playoffs Table::$1:$1)


You should get the result 3/1. If you do (and even if you don't), put the COLUMN() function around it and see what column it is giving you. Is it the correct column? AE = 26+5=31, right? If not the correct column, look at the column it returned and maybe it will be clear what is going on, hopefully.


I've not seen COLUMN(LOOKUP()) used before. The usual function is MATCH. But it seems to work. Is that documented somewhere?

Reply
User profile for user: Badunit
Badunit
User level: Level 7
30,336 points

Mar 1, 2024 4:09 PM in response to AndyK2015

That is one wild looking chart!


Because it is a problem with a change in A1, I'm starting my search there.


The formula uses A1 to do a lookup in Make Playoffs::Table 1 and the formula creates an address in that table. The problem may be in that table. Can you post it?


I don't know what the purpose of REGEX($A$1) is in the formula. That's a weird use of REGEX. If A1 is a date&time value, REGEX turns it into a string. $A$1&"" will do the same thing. If A1 is already a string (text) then neither does a thing. I don't think this is anything to do with the problem, though.


Reply
User profile for user: AndyK2015
AndyK2015 Author
User level: Level 1
4 points

Mar 1, 2024 4:24 PM in response to Badunit

Hi Badunit,


Thanks for the reply! Below is a screenshot of the Make Playoffs sheet. I have a bunch of hidden columns, but I know that isn't causing an issue, especially because both 2/29 and 3/1 are unhidden at the moment.


I tried removing the REGEX function from that formula. Using $A$1&"" produced an error (hidden by the IFERROR function). When I removed the IFERROR function, the error produced says "LOOKUP couldn’t find the requested value." I tried substituting it with just $A$1, but that produced the same error. A1 is formatted as text. Changing the formatting doesn't affect anything it seems.

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

My Table is Bugging and I Don't Know Why...

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up