Sheet Reference Comparing two Totals to determine Indicator

Hello,

I am working on a formula where I am referencing one sheet with numbers to set an indicator on another, to determine if something is Red, yellow or green.

I am using a single cell called difference on my reference Sheet. The code should read if Less than 20,000.00 it should be Red, greater than 100,000.00 should be green, or it should be yellow

I tried some basic stuff just to see if I could get the indicator to work but nope

=IF({Difference}, @cell < “20000.00”, Red, IF({Difference}, @cell >= "100000.00=, Green, Yellow))

=IF({Difference}, @cell < “$20,000.00”, Red, IF({Difference}, @cell >= "$100,000.00", Green, Yellow))

=IF({Difference}, @cell < “20000”, Red, IF({Difference}, @cell >= "100000", Green, Yellow))

I am literally referencing a single cell on the reference sheet. Any Help would be appreciated.


Should I be using at Row? 😕

Caroline

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Putting quotes around numbers in a formula changes it to a text string (which can't be less than or greater than a number).


    Try removing the quotes from the numbers and putting them around the colors.


    I also notice that your quotes are slanted. Unless you have them "italicized", these are the wrong types of quotes.

    They are called "smart quotes" which (ironically enough) are not valid characters in a Smartsheet formula. You are going to want to retype them here, in Smartsheet directly, or in a text editor such as Notepad (not Word). You will see that typing them in the previously mentioned places have them straight up and down. These are the quotes you are looking for.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey @delaurellc

    You're almost there! I hope you don't mind me jumping in @Paul Newcome

    For an IF statement, the first part of it is the logic statement and then you put a comma. Right now you're listing a range then the comma, so it's getting confused as to what your logic is.

    As long as the {reference} is only to a single cell, you can jump right into the comparison with the number instead of using @cell

    Try:

    =IF({Difference} < 20000, "Red", IF({Difference} >= 100000, "Green", "Yellow"))


    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!