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).
-
@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?
-
@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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!