Every 13 cells could be summing the total of multiple years of data. Eg 12 month + 1 total, rinse and repeat. Then summing just the total cells for each year. š
Wait, that's an actual formula. When I first saw this I thought it was an employee going insane and keysmashing into a spreadsheet as evidenced by the "send help".
Which is correct, but obviously the calculations would be best served by reorganizing the table and probably using a pivot table or at least grouping and subtotaling.
I must find the monstrous function which indexes virtual arrays that I implemented in a Google sheet of mine when I discovered there's no limit to how reckless you can be in a single cell.
Edit: Here it is:
ARRAYFORMULA(IFERROR((INDEX('Invoice History'!$A$7:$A$44, SMALL(IF(($G$3='Invoice History'!$D$7:$D$44)*('Invoice History'!$F$7:$F$44="Unpaid"), MATCH(ROW('Invoice History'!$A$7:$A$44), ROW('Invoice History'!$A$7:$A$44)), ""), ROW(A1))))))
According to my documentation, it is used for "Looking up a value on a row, on another sheet, where two other values on that row match the given criteria"
I have written similarly awful formulae when I needed to get a csv to export to Google Calendars, but the program I was exporting to wasnāt using Googleās accepted csv headers.
I ended up creating a template that looked at the exported csv file, and then reformatted it in a way that Google liked (and added some extra info along the way.) I needed it to only fill text if an entry actually had info in it, and hide all the text otherwise. So that I could automatically delete empty cells and avoid a bunch of empty calendar entries when importing it into Google. The resulting formula for some of the fields wasā¦ Not great. This is what controlled the ānameā of each calendar event:
It takes several different potential fields, and combines them into a single field. If there are no entries, it gets left blank.
And every single time I would get it working properly, someone would add a row or change the data validation rules, so I would have to go in and update my formulae. After the fifth or sixth time that happened, I told the person making the changes that it was his job to update the formulae. Suddenly, it stopped getting changed.
Damn. At that point I would just switch over to a macro. Although I also have some formulas that xlookup a value, concatenate it, and the xlookup the new concatenated value.
17 Comments
CaptainBasculin@lemmy.ml · 34 pts · 1y
virgin =SUM() users vs chad click at each cell enjoyers
LeFrog@discuss.tchncs.de · 17 pts · 1y
What is even happening here? There is a sum of like every 13th cell?
Enkers@sh.itjust.works · 9 pts · 1y
My guess is that there are multiple tables on one sheet, and this is the summation of the sub-totals.
sunbrrnslapper@lemmy.world · 4 pts · 1y
Multiple things formatted to look like tables, but not actually tables. And they are summing like every 13th cell. It is wild over here.
fulcrummed@lemmy.world · 5 pts · 1y
Every 13 cells could be summing the total of multiple years of data. Eg 12 month + 1 total, rinse and repeat. Then summing just the total cells for each year. š
Aarkon@discuss.tchncs.de · 1 pts · 1y
I guess this is Excel.
rustyfemboy@lemmy.blahaj.zone · 8 pts · 1y
Wait, that's an actual formula. When I first saw this I thought it was an employee going insane and keysmashing into a spreadsheet as evidenced by the "send help".
sunbrrnslapper@lemmy.world · 5 pts · 1y
Yup, actual formula. Might not even be the most offensive one in the spreadsheet.
TempermentalAnomaly@lemmy.world · 6 pts · 1y
Wait till AI is able to help.
sunbrrnslapper@lemmy.world · 3 pts · 1y
OMG. Which AI tool produced this?
TempermentalAnomaly@lemmy.world · 4 pts · 1y
Claude. To be fair, it had trouble OCR-ing it. After way too much back and forth about the pattern, it produced this:
Which is correct, but obviously the calculations would be best served by reorganizing the table and probably using a pivot table or at least grouping and subtotaling.
Agent641@lemmy.world · 5 pts · 1y
I must find the monstrous function which indexes virtual arrays that I implemented in a Google sheet of mine when I discovered there's no limit to how reckless you can be in a single cell.
Edit: Here it is:
ARRAYFORMULA(IFERROR((INDEX('Invoice History'!$A$7:$A$44, SMALL(IF(($G$3='Invoice History'!$D$7:$D$44)*('Invoice History'!$F$7:$F$44="Unpaid"), MATCH(ROW('Invoice History'!$A$7:$A$44), ROW('Invoice History'!$A$7:$A$44)), ""), ROW(A1))))))According to my documentation, it is used for "Looking up a value on a row, on another sheet, where two other values on that row match the given criteria"
collapse_already@lemmy.ml · 5 pts · 1y
Resume: "Experienced Excel Wizard"
Mic_Check_One_Two@reddthat.com · 4 pts · 1y
I have written similarly awful formulae when I needed to get a csv to export to Google Calendars, but the program I was exporting to wasnāt using Googleās accepted csv headers.
I ended up creating a template that looked at the exported csv file, and then reformatted it in a way that Google liked (and added some extra info along the way.) I needed it to only fill text if an entry actually had info in it, and hide all the text otherwise. So that I could automatically delete empty cells and avoid a bunch of empty calendar entries when importing it into Google. The resulting formula for some of the fields wasā¦ Not great. This is what controlled the ānameā of each calendar event:
It takes several different potential fields, and combines them into a single field. If there are no entries, it gets left blank.
And every single time I would get it working properly, someone would add a row or change the data validation rules, so I would have to go in and update my formulae. After the fifth or sixth time that happened, I told the person making the changes that it was his job to update the formulae. Suddenly, it stopped getting changed.
clay_pidgin@sh.itjust.works · 1 pts · 1y
That's bonkers but I can follow it, I think. Good work!
runner_g@lemmy.blahaj.zone · 1 pts · 1y
Damn. At that point I would just switch over to a macro. Although I also have some formulas that xlookup a value, concatenate it, and the xlookup the new concatenated value.
Vegeta@lemmy.ca · 3 pts · 1y
I winced.