IF function with an additional modifier

Hello,

I have a basic question that I can't seem to find any answer for. Is it possible to use the IF function and have the result modified in the same cell by a value in a different cell?

So here is my formula for the display column:

=IF([Code 1]@row = "Overtime", "$2.00", IF([Code 1]@row = "Doubletime", "$4.00"))

Now, lets say that Jin gets a $1.50 bonus added to his rate no matter what. Is there a way to shove another IF function into this formula like [Name]@row = "Jin", "+$1.50". I would like it to display as "$4.00 + $1.50" instead of "$5.50" if possible.

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    I would add in another row for the bonus. From there you can use the formula below. You might need to add an apostrophe in front of the bonus number if you intend to have zeroes at the end. For example input '$1.50 instead of $1.50 so Smartsheet doesn't convert the amount to a value.

    =IF([Code 1]@row = "Overtime", "$2.00", IF([Code 1]@row = "Doubletime", "$4.00")) + IF(Bonus@row = "", "", " + " + Bonus@row)
    

    If you intend to use names things can get more complex because they are variable but to answer your question you can insert the name into the formula as an option

    =IF([Code 1]@row = "Overtime", "$2.00", IF([Code 1]@row = "Doubletime", "$4.00")) + IF(Name@row = "Jin", " + $1.50", "")
    

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    I would add in another row for the bonus. From there you can use the formula below. You might need to add an apostrophe in front of the bonus number if you intend to have zeroes at the end. For example input '$1.50 instead of $1.50 so Smartsheet doesn't convert the amount to a value.

    =IF([Code 1]@row = "Overtime", "$2.00", IF([Code 1]@row = "Doubletime", "$4.00")) + IF(Bonus@row = "", "", " + " + Bonus@row)
    

    If you intend to use names things can get more complex because they are variable but to answer your question you can insert the name into the formula as an option

    =IF([Code 1]@row = "Overtime", "$2.00", IF([Code 1]@row = "Doubletime", "$4.00")) + IF(Name@row = "Jin", " + $1.50", "")
    
  • Dan B.
    Dan B. ✭✭✭✭

    So I took your advice and just created an additional column. I'm going to circle back and try this later, but I think it looks good.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!