Harvey Balls / Percentage %

Options

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Is the [Project Arch HRS] column manually populated?

  • Rosa Crisostomo
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!