Formula for %
Im looking for a formula to return the percentage that our projects are over/under.
Best Answers

If you want to know the percentage over/under the Contract Amount, your formula (placed the [Percentage] column) would be:
=([Contract amount]@row  [Install Labor (actual)]@row) / [Contract amount]@row
Be sure the "Percentage" column is formatted as a percentage. Positive numbers show that your total spend is under the [Contract amount]. Negative values show your total spend is over.
You can use a similar formula to measure how far over/under your [Labor $ (quoted)] amount is from your [Install Labor (actual)] amount.
=([Labor $ (quoted)]@row  [Install Labor (actual)]@row) / [Labor $ (quoted)]@row
Here, though, a negative value shows that you are OVER the estimate. A positive value shows you are at or UNDER the estimate.

Here's what I suggest 
Add the [Labor Hourly Rate] column and enter the hourly rate for the labor on that project. (I've assumed $30/hour for each row in this example.)
Add the [Labor Hours (quoted)] column and use this formula: =[Labor $ (quoted]/[Labor Hourly Rate]
This gives you the number of labor hours estimate for the project.
Add the [Labor Hours (actual)] column and use this formula: =[Install Labor (actual)]/[Labor Hourly Rate]
This gives you the actual number of labor hours worked for the project.
Now, add the [Labor Hours over/under] column and use this formula: [Labor Hours (actual)][Labor Hours (quoted)]
A positive number tells you the number of hours OVER the quoted amount. A negative value shows the number of hours UNDER the quote.
Answers

If you want to know the percentage over/under the Contract Amount, your formula (placed the [Percentage] column) would be:
=([Contract amount]@row  [Install Labor (actual)]@row) / [Contract amount]@row
Be sure the "Percentage" column is formatted as a percentage. Positive numbers show that your total spend is under the [Contract amount]. Negative values show your total spend is over.
You can use a similar formula to measure how far over/under your [Labor $ (quoted)] amount is from your [Install Labor (actual)] amount.
=([Labor $ (quoted)]@row  [Install Labor (actual)]@row) / [Labor $ (quoted)]@row
Here, though, a negative value shows that you are OVER the estimate. A positive value shows you are at or UNDER the estimate.

Danielle, thanks this helped tremendously. Im also trying to show hours over based on $30 per hour. Is that even possible?

Here's what I suggest 
Add the [Labor Hourly Rate] column and enter the hourly rate for the labor on that project. (I've assumed $30/hour for each row in this example.)
Add the [Labor Hours (quoted)] column and use this formula: =[Labor $ (quoted]/[Labor Hourly Rate]
This gives you the number of labor hours estimate for the project.
Add the [Labor Hours (actual)] column and use this formula: =[Install Labor (actual)]/[Labor Hourly Rate]
This gives you the actual number of labor hours worked for the project.
Now, add the [Labor Hours over/under] column and use this formula: [Labor Hours (actual)][Labor Hours (quoted)]
A positive number tells you the number of hours OVER the quoted amount. A negative value shows the number of hours UNDER the quote.
Help Article Resources
Categories
Check out the Formula Handbook template!