IF with Summary Sheet Information
=IF(Jan2 >= 1, [Baseline = 1-250 Devices Shipped]#, IF(Jan2 <= 250, },[Baseline = 1-250 Devices Shipped]#), IF(Jan2 >= 251, [Tier 1 = 251-500 Devices Shipped]#, IF(Jan2 <= 500, [Tier 1 = 251-500 Devices Shipped]#), IF(Jan2 >= 501, [Tier 2 = 501-1,000 Devices Shipped]#, IF(Jan2 <= 1000, [Tier 2 = 501-1,000 Devices Shipped]#), IF(Jan2 >= 1001, [Tier 3 = 1,001-1,500 Devices Shipped]#, IF(Jan2 <= 1500,[Tier 3 = 1,001-1,500 Devices Shipped]#), IF(Jan2 >= 1501, [Tier 4 = 1,501-2,000 Devices Shipped]#, IF(Jan2 <= 2000,[Tier 4 = 1,501-2,000 Devices Shipped]#), IF(Jan2 >= 2001, [Tier 5 = 2,001-2,500 Devices Shipped]#, IF(Jan2 <= 2500, [Tier 5 = 2,001-2,500 Devices Shipped]#), IF(Jan2 >= 2501, [Tier 6 = 2,501-3,000 Devices Shipped]#, IF(Jan2 <= 3000, [Tier 6 = 2,501-3,000 Devices Shipped]#, 0)))))))))
Returning
#UNPARSEABLE
Answers
-
You have some closing parenthesis tucked in where they don't belong as well as an extra piece there after the second IF.
All closing parenthesis for the IF functions should come at the very end of the formula. The way you transitioned from the first IF to the second IF is how it should be done for all of them. You also have some extra characters there after the logical statement portion of the second IF.
-
OK got all that figured out so thank you but now it stops at the first tier. I think its because its grater that 1, I think I need an OR so it would be Greater than or Equal to 1 or Less Than or Equal to 250? Does that make sense?
-
Nested IF formulas will stop on the first true value. This means they will skip any false arguments that come before the true. You can leverage this to avoid using AND and OR statements by structuring your nested IFs.
=IF(This<= 1, 0, IF(This<= 10, 10, IF(This<= 15, 15)))
The above will output zero for anything less than or equal to 1, ten for anything between 2 and 10, and fifteen for anything between 11 and 15.
-
Ok did this and getting #UNPARSEABLE
=IF(Jan4 = 1, [Baseline = 1-250 Devices Shipped]#, IF(Jan4 <= 250, [Baseline = 1-250 Devices Shipped]#, IF(Jan4 = 251, [Tier 1 = 251-500 Devices Shipped]#, IF(J4 <= 500, [Tier 1 = 251-500 Devices Shipped]#, IF(J4 = 501, [Tier 2 = 501-1000 Devices Shipped]#, IF(J4 <= 1000, [Tier 2 = 501-1000 Devices Shipped]#))))))
-
Neverminded I figured it out :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!