I've been using sheet summary formulas for a week but have been struggling with something I don't understand all afternoon. * I have a summary field called % Planned Complete. For simplification, I'm testing this formula: =INDEX([% Planned Complete]:[% Planned Complete], 21) * 100 But it won't return the value for row 21 in the % Planned Complete column. Instead, I see a message "Divide by 0." So I'm doing some troubleshooting. [% Planned Complete] was a column calc, and I converted it to a cell calc so I could test my own values. * The formula was calculating the value 100%. I hardcoded it to be .3. The summary statistic still shows a value of "Divide by 0" * Row 1 of [% Planned Complete] has a "Divide by 0" error. Perhaps for a reason I don't know the summary formula is letting row 1 override its search for row 21. * I hardcoded the value of row 1 to .2, and I predicted that the summary statistic would change the value to 20. * But, no, now the summary statistic shows the value of 30. It's as if my summary statistic calculates correctly … unless there's a Divide by 0 error on row 1, and then the statistic shows a Divide by 0 value. Does that make sense to anyone? I also tried this. I changed the summary statistic to =[% Planned Complete]21 and I changed the row 1 formula back to the formula that gives a Divide by 0 error. In this case, the summary statistic still shows .3.

I don't understand my Sheet Summary formula

I've been using sheet summary formulas for a week but have been struggling with something I don't understand all afternoon.

I have a summary field called % Planned Complete. For simplification, I'm testing this formula: =INDEX([% Planned Complete]:[% Planned Complete], 21) * 100

But it won't return the value for row 21 in the % Planned Complete column. Instead, I see a message "Divide by 0."

So I'm doing some troubleshooting. [% Planned Complete] was a column calc, and I converted it to a cell calc so I could test my own values.

The formula was calculating the value 100%. I hardcoded it to be .3. The summary statistic still shows a value of "Divide by 0"
Row 1 of [% Planned Complete] has a "Divide by 0" error. Perhaps for a reason I don't know the summary formula is letting row 1 override its search for row 21.
I hardcoded the value of row 1 to .2, and I predicted that the summary statistic would change the value to 20.
But, no, now the summary statistic shows the value of 30.

It's as if my summary statistic calculates correctly … unless there's a Divide by 0 error on row 1, and then the statistic shows a Divide by 0 value.

Does that make sense to anyone?

I also tried this. I changed the summary statistic to
=[% Planned Complete]21
and I changed the row 1 formula back to the formula that gives a Divide by 0 error. In this case, the summary statistic still shows .3.

Find more posts tagged with

Sheets

Formulas

Sheet Summary

Accepted answers

Paul Newcome

If that error is in any cell being referenced within a range, it will pull through. My suggestion would be to use an IFERROR on the formula in the referenced column to output something other than an error on the row(s) that have it within the reference range.

=IFERROR(current_formula_throwing_error, "")

All comments

jmyzk_cloudsmart_jp

Hi @CLH

To understand your problem, I created a test sheet as shown below.

The first Sheet Summary field has the same formula as yours.

=INDEX([% Planned Complete]:[% Planned Complete], 21)

As you can see, since the second row of the range [% Planned Complete]:[% Planned Complete] has a divide by zero error, the field is showing the DIVIDE BY ZERO.

I guess the Smartsheet INDEX function returns the error the moment it references a range with the error, regardless of the row index where you do not have the error.

So, I added the [% Planned Complete 2] column with the following formula and referenced the range in the second field.

=IF(B@row <> 0, A@row / B@row)

You could do the same with your [% Planned Complete] column to avoid the error.

https://app.smartsheet.com/b/publish?EQBCT=7ee6d1cf4a2945748d9e99b9e3f4dbe1

Paul Newcome

=IFERROR(current_formula_throwing_error, "")

CLH

Thanks guys.

I was thinking of the same possibility last night — to summarize, that since this is a summary statistic referencing an entire column, then any error on the sheet will cause the summary to fail

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.