IF Formula calculation Error
Hi
I have the following formula, which works up to a certain point. Let me explain.
=IF(ISBLANK([Original Total Cost]@row), "No", IF([Actual Total Cost]@row > [Original Total Cost]@row, "Yes", "No"))
When the Original Total Cost value is >the Actual Total Cost, the answer is "Yes" - perfect, that's what I need.
When the original Total Cost value is blank, the return response is "No" - again working as I require it to. But when I enter a value in the blank cell, whether the value is greater than or less than the return response is "Yes" and I don't know how to correct it.
Some guidance towards a solution would be appreciated.
Answers
-
How is the "Actual" column being populated? It sounds to me like it may be being stored on the back-end as a text value (numbers are always "greater than " text).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Correct. The "Actual" column is being populated from a form submission. The field is "text/number" and on the form under validation I have selected number only as it can only be a number that populates in that cell from the submitter.
-
Are you able to show some examples of those numbers in the sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome I created a copy and entered bogus figures. Snapshot below
-
Are there times where decimals will be used, or are they always ".00"? Have you tried turning off the number validation in the form?
I'd like to try a quick test. Insert a temporary helper column and enter the below column formula:
=IF(NOT(ISNUMBER([Actual Cost Total]@row)), "Nope")
Apply a filter to this helper column to show rows where the helper column is not blank. What happens then?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!