Too many formula references
I've got a page that's running into an error for having too many cells referenced. The problem is that the page is a list of samples, where samples are grouped into lots (40 samples per lot). I need to do a lot of comparisons within a given lot, which means I'm constantly using formulas that group those lots, such as:
=MAX(COLLECT(SampleNum:SampleNum, [Lot]:[Lot], [Lot]@row, Reviewer:Reviewer, Reviewer@row, Date:Date, Date@row))
Because there are about a thousand samples, and growing, it doesn't take long before these lookups reference a lot of cells.
Questions:
1.) Would the order of this example formula reduce the number of references that Smartsheet counts against me? For instance if the match for "Reviewer:Reviewer, Reviewer@row" is 100 samples, and "Lot:Lot, Lot@row" is 40 samples, and "Date:Date, Date@row" is 3 samples, would it make sense to arrange the formula order so Date was the first criteria, Lot was the second, and Reviewer was the third? I'm not talking about processor burden, I'm talking about Smartsheet cell count, which I think is some kind estimate of theoretical maximum.
2.) When a lot is complete, I don't need to perform calculations on it anymore, and I have a simple True/False flag on every sample row to indicate if the overarching lot is complete. Would adding an IF statement to shut the calculations down if the lot is complete reduce the number of references Smartsheet counts against me? For instances:
=IF([Complete?]@row="True", "", MAX(COLLECT(SampleNum:SampleNum, [Lot]:[Lot], [Lot]@row, Reviewer:Reviewer, Reviewer@row, Date:Date, Date@row)))
3.) Within the sheet, the samples are all children of the main lot--I've tried to figure out a way to only reference other children within a parent group using PARENT and CHILD, but it feels like I need something more akin to a "SIBLING" reference. If this existed, I could arrange the original calculation as:
=MAX(COLLECT(SIBLING(SampleNum@row), SIBLING(Reviewer@row), Reviewer@row, SIBLING(Date@row), Date@row)
I tried using a nested statement to get the same effect, i.e. CHILDREN(PARENT(SampleNum@row)), but no dice. Any way to perform calcs on sibling rows, other than my method of "LOT:LOT, LOT@row"? It feels like being able to reference siblings would reduce my referenced cells dramatically.
Thanks for any thoughts and input!
Best Answer
-
Changing the order of the pieces within a formula will not change the number of references because you are still referencing entire columns. Same with #2. The references are still there, so you are still referencing entire columns. an additional note to that... Even if there are only 3 matches, there are still 100 cells being evaluated because there are 100 rows (for example).
#3 would be a great idea, and I definitely suggest submitting a product enhancement request when you have a moment.
Regarding your additional thought/comment... That is the same as #1 and #2. Because you have entire columns referenced, you are still going to have the entire column counted. the second SUMIFS is going to double your cells referenced count because now you are referencing a second column.
The only options I can think of would be to separate the data out across multiple sheets or archiving rows that you no longer need to run these formulas on.
Answers
-
Similarly, I don't know if the following change increases or decreases the number of referenced cells. (Both calculations just create a reference number for me that have the same practical value):
Column Name: "Summed Complete"
FROM:
=SUMIFS(Complete:Complete, [Row ID]:[Row ID], <=[Row ID]@row))
TO: (Addition to specify the lot)
=SUMIFS(Complete:Complete, [Row ID]:[Row ID], <=[Row ID]@row, [Lot]:[Lot], [Lot]@row))
The first SUMIF adds a LOT more values because it's adding every proceeding row of an ever-increasing table, but the second one actually requires that, for every cell in the "Summed Complete" column, the system must review every row in my "Lot" column to to see if the lot number is the same. So the second would seem to have more referenced cells, while the first adds more numbers. But I don't know? Does increasing the number of criterion ranges (i.e. increasing the specificity) add value or should you keep your criterion ranges to a minimum?
-
Changing the order of the pieces within a formula will not change the number of references because you are still referencing entire columns. Same with #2. The references are still there, so you are still referencing entire columns. an additional note to that... Even if there are only 3 matches, there are still 100 cells being evaluated because there are 100 rows (for example).
#3 would be a great idea, and I definitely suggest submitting a product enhancement request when you have a moment.
Regarding your additional thought/comment... That is the same as #1 and #2. Because you have entire columns referenced, you are still going to have the entire column counted. the second SUMIFS is going to double your cells referenced count because now you are referencing a second column.
The only options I can think of would be to separate the data out across multiple sheets or archiving rows that you no longer need to run these formulas on.
-
Thanks Paul! I finally just sat down and did those calculations this weekend, figured out the problem with referencing another column grows exponentially with the number of rows. I also ran some tests to see what killed processing time in Smartsheet. I’m going to create a summary for Smartsheet users at my company and I’ll post those notes on the community pages here for reference. I’ll also put in that enhancement request — would be nice to have. Another request I’m going to put in is to create an “archive” checkbox. Essentially, if you check that box for a row, calculations won’t run on that row and they won’t count towards your cell count (or processing). Seems like a big win for users and Smartsheet, since they could reduce their processing overhead. Maybe allow users to access those columns with additional code, such as:
ARCHIVE()
-
That would be nice, but I feel like it would definitely be a challenge to implement. I personally just archive over to another sheet to capture the static data and free up space on my working sheet.
