Children Parent % not calculating

Hello,

I'm trying to build a sheet that calculates a percentage off of a Status column, but the column is not returning a percentage and the =AVG(Children) section. This is my mess of a formula

=IF(IsParent@row = 0, IF(OR([Rally Status]@row = "Submitted", [Rally Status]@row = "Acknowledged", [Rally Status]@row = "Not Started/Backlogged", [Rally Status]@row = "0", [Rally Status]@row = "Defined/Ready"), ".15", (IF([Rally Status]@row = "In Development", ".25", (IF(OR([Rally Status]@row = "QA Testing", [Rally Status]@row = "UAT Testing", [Rally Status]@row = "Customer Testing"), ".5", (IF([Rally Status]@row = "Prod Ready", ".8", (IF([Rally Status]@row = "Completed", "1", (IF([Rally Status]@row = "Canceled", "0", (IF([Rally Status]@row = "On Hold", "0", "0"))))))))))))), =AVG((CHILDREN())))

Here is a screenshot of the Sheet.

Any help or a better solution is much appreciated.

Find more posts tagged with

Sheets

Formulas

Accepted answers

Genevieve P.

Hi @Dakota Haeffner

A couple of things to note here. First of all, if you're looking to return a percent, you'll want to use decimals as you've done, however take away the "quotes" around the numbers. Quotes will turn numbers into text and so your % formatting won't be applied:

You'll notice that in your Child cells the numbers appear on the left of the cell. This indicates that they're seen as Text (because of the "quotes"), which also means they cannot be Averaged.

See: Frequently asked questions about using formulas

Secondly, with the =AVG(CHILDREN at the end of the formula, make sure you remove the = sign. You will only want that at the very start of the entire formula, or as a logic statement (like this = that).

Try This:

=IF(IsParent@row = 0, (IF(OR([Rally Status]@row = "Submitted", [Rally Status]@row = "Acknowledged", [Rally Status]@row = "Not Started/Backlogged", [Rally Status]@row = "0", [Rally Status]@row = "Defined/Ready"), 0.15, IF([Rally Status]@row = "In Development", 0.25, IF(OR([Rally Status]@row = "QA Testing", [Rally Status]@row = "UAT Testing", [Rally Status]@row = "Customer Testing"), 0.5, IF([Rally Status]@row = "Prod Ready", 0.8, IF([Rally Status]@row = "Completed", 1, IF([Rally Status]@row = "Canceled", 0, IF([Rally Status]@row = "On Hold", 0, 0)))))))), AVG(CHILDREN()))

Cheers,

Genevieve

All comments

Dakota Haeffner

It is worth mentioning I do have the percentage button clicked for the column.

Genevieve P.

Hi @Dakota Haeffner

You'll notice that in your Child cells the numbers appear on the left of the cell. This indicates that they're seen as Text (because of the "quotes"), which also means they cannot be Averaged.

See: Frequently asked questions about using formulas

Try This:

Cheers,

Genevieve

Dakota Haeffner

@Genevieve P.,

That worked perfectly! I didn't know about " " making things text and that'll actually help with some other formulas I'm building too! Thank you for the assistance!

Bridget Clawson Braaten

I had the same issue, once I knew the dependencies and predecessors were all set up, I made sure to put start and end dates in, that made the difference and the % percentage calc finally worked syncing child and parent %.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.