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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!