Formula for %

Options

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



Tags:

Best Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    TPALJA ✭✭
    Options

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

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    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!