Formula for %

TPALJA

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

Danielle Arteaga

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 Arteaga

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.

Danielle Arteaga

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.

TPALJA

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

Danielle Arteaga

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.