Harvey Balls / Percentage %

Hi,

I am needing some help with the formula that triggers the Harvey ball.

In the sheet below, we have a certain number of hours assigned in the "Project Arch HRS". We then update the number of cumulative hours each week in "PA Actual". In "PA % Complete" we have a formula that calculates the % of hours complete: =IFERROR(SUM([PA Actual]@row / [Project Arch HRS]@row), 0).

It is odd, because the same formula is entered in all three cells in "PA % Complete" but the last one calculates to 1.01, and I cannot figure out why.

In the column "Project Arch Progress" we have the following formula:

=IF([PA % Complete]@row < 0.25, "Empty", IF([PA % Complete]@row < 0.5, "Quarter", IF([PA % Complete]@row < 0.75, "Half", IF([PA % Complete]@row < 1, "Three Quarter", "Full"))))

My client would like to have a visual of the progress but despite being 100% complete, the Harvey ball only progresses to 75%.

What is even more strange is, the formulas are all the same for another phase (under brown), but the PA % Complete and the Harvey Ball calculate correctly.

Here are the formulas for the phase (under brown) in the picture above:

PA % Complete: =IFERROR(SUM([PA Actual]@row / [Project Arch HRS]@row), 0)

Project Arch Progress: =IF([PA % Complete]@row < 0.25, "Empty", IF([PA % Complete]@row < 0.5, "Quarter", IF([PA % Complete]@row < 0.75, "Half", IF([PA % Complete]@row < 1, "Three Quarter", "Full"))))

Thanks for your help!! Please let me know if you need clarification on anything.

Rosa

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Is the [Project Arch HRS] column manually populated?

Rosa Crisostomo

Hi Paul,

It is formula driven. Here is the formula and a screenshot:

=SUM([Working Budget]@row * [Project Arch %]@row) / [Project Arch HRS]11

**The cells in the formula are in red/bold font.

Thanks again and sorry for the delayed response!

Rosa Crisostomo

Hi Paul,

Following up to see if you had a chance to look into this? Please let me know if you need further clarification.

Thanks!

Paul Newcome

Sorry for the delay. I must have missed the notification from your response.

It looks to be a rounding issue. Assuming [Project Arch HRS]11 is the cell with 225.00 in bold red, then this formula:

=SUM([Working Budget]@row * [Project Arch %]@row) / [Project Arch HRS]11

in the [Project Arch HRS] column on the row where it is showing 101% would actually generate a result of

0.39635

So when you enter 0.40 in the [PA Actual] column, you are entering a number greater than the result of the formula in the [Project Arch HRS] column which is why you are getting the 101%.

I would suggest using a ROUND function on the [Project Arch HRS] formula so that it is storing the same data on the back-end as what it is displaying.

=ROUND(SUM([Working Budget]@row * [Project Arch %]@row) / [Project Arch HRS]11, 2)

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.