# Formula for %

âś­âś­âś­

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

Tags:

• âś­âś­âś­âś­âś­âś­

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.

• âś­âś­âś­âś­âś­âś­

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!