Which is better in this case = Nested IF statement or using two formulas related to other columns

coltonlbaker

Hi,

 In the spirit of efficiency of memory usage, I want to figure out which methodology would be better:

 

In either case, A/B are columns used to toggle several sheet functions as they relate to the heirarchy level the row is.

1. Use a nested IF statement: =IF(A@row = 0, SUM(CHILDREN()), IF(B@row=0, INDEX({REF Share}, MATCH([Applicant Name]@row, {Applicant Name}, 1))))
2. Use alternating IF statements assigned to the parent and child row types: 

=IF(A@row = 0, SUM(CHILDREN()))

=IF(B@row=0, INDEX({REF Share}, MATCH([Applicant Name]@row, {Applicant Name}, 1))))

 

Will one of these be significantly better on memory usage than the other in a large, dynamic dataset? I expect the sheet to grow to potentially max out the sheet and spill over into an additional sheet.

Does anyone foresee issues arising in either case?

Additionally, I intend to have my team populate rows via forms so that formulas remain untouched.

Eric M Oliveira

Hello,

 

When it comes to which of the above formula would be less of a burden on memory, in theory, the less the formula has to do the better. However, at the end of the day, it all comes down to your desired workflow. 

 

If it was me I would use the first formula and remove the need for multiple columns. You may want to use a single column such as a symbol column which has multiple values to remove the need for multiple columns. For example:

 

=IF(A@row = "Green", SUM(CHILDREN()), IF(A@row= "Yellow", INDEX({REF Share}, MATCH([Applicant Name]@row, {Applicant Name}, 1))))

 

Slowness on a sheet can be influenced by several different factors. Some of the main factors:  

 

Browser speed - browser speed can be one of the main contributors of slowness when loading, navigating, and saving a sheet. In our tests we have found Smartsheet to perform best using Google Chrome then Firefox. If it is an option or when troubleshooting, we recommend trying one of these browsers to see if it improves your response times.

 

Advanced Functionality - widespread use of formulas, cell-linking, and conditional formatting can contribute to slower load times. If your sheet starts to slow down, try disabling conditional formatting rules to see if this is contributing to the issue. Also consider getting rid of formula columns or cell-links which might no longer be needed. 

 

Sheet size - as sheets get larger, performance may decrease. When appropriate, move rows (http://help.smartsheet.com/customer/portal/articles/504748) to other sheets if they have been completed or are no longer needed on your active sheet.

Also consider breaking up sheets into logical divisions, such as by month, then using Reporting (http://help.smartsheet.com/customer/portal/articles/522214) to review rows from different sheets as needed.

 

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support