I have one column Miles and a second column Billable miles.

MILES BILLABLE MILES (RESULTS)

IF miles is > 1 miles -500, if miles < 500 "N/A"

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @GLW

    Thank you for clarifying! We can add a statement at the beginning that says If MILES in the cell of this row is less than 500, return "N/A".

    Otherwise (meaning the value is either 500 or more), then we can use your formula to subtract 500.


    Try this:

    =IF(Miles@row <= 500, "N/A", Miles@row - 500)


    I've used @row so you can make this a Column Formula if you'd like!

    Cheers,

    Genevieve

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    =IF({Information provided}<{Information Required}, "Provide more info", "")

  • GLW
    GLW ✭✭

    The first column MILES contains the mileage to our venue. If the mileage is over 500, we charge an additional fee for the extra miles. the extra miles will be in the second column BILLABLE MILES which is where the formula resides. the formula below works but if the mileage is 500 or less, the billable miles column shows a negative number. In lieu of the negative number, i would like it to return an N/A.

    =IF(Miles1 > 1, Miles1 - 500)

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @GLW

    Thank you for clarifying! We can add a statement at the beginning that says If MILES in the cell of this row is less than 500, return "N/A".

    Otherwise (meaning the value is either 500 or more), then we can use your formula to subtract 500.


    Try this:

    =IF(Miles@row <= 500, "N/A", Miles@row - 500)


    I've used @row so you can make this a Column Formula if you'd like!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin

    Wonderful! I'm glad I could help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!