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
Answers

Is the [Project Arch HRS] column manually populated?

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!

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!

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 backend as what it is displaying.
=ROUND(SUM([Working Budget]@row * [Project Arch %]@row) / [Project Arch HRS]11, 2)
Help Article Resources
Categories
Check out the Formula Handbook template!