Harvey Balls / Percentage %

Rosa Crisostomo

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

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)