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

Find more posts tagged with

Accepted answers

Paul Newcome

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.

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

All comments

Paul Newcome

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.

delaurellc

Hi Paul,

Must be getting tired of me by now, did what you said and not working

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

Error is getting incorrect Argument Set

Genevieve P.

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

delaurellc

Genevieve that worked. Thank you! I have another question coming out in a few mins. I am adding to a string by itself so if others have the issues they can search it and get the answer!

You and Paul are Awesome!

I am learning alot!

Paul Newcome

@Genevieve P. Not at all. I say the quote types and placements and didn't even notice the commas.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.