IF Statement for a range of numbers
Good afternoon,
I am trying to get a cell to auto populate with a specific word if another cell has a specific range of numbers in it. I keep getting an #unparseable but I found this formula in the community and it worked for someone else trying to perform the same thing.
I have 5 tiers of numbers to work from and my formula currently looks like this:
=IF([Notification Booked Billing Amount]1@row < $1,000.00, "SMB - LOW", IF((Notification Booked Billing Amount]1@row < $3,000.00, "SMB - HIGH", IF([Notification Booked Billing Amount]1@row < $5,000.00, "MID Low", IF([Notification Booked Billing Amount]1@row < $10,000.00, "MID High", "Key Account")))
What am I missing?
Comments
-
Hey Nic,
That doesn't seem to fix the issue. Any other ideas?
-
Hi Jess,
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I got it! Here's my updated formula:
=IF([Notification Booked Billing Amount]1 < 1000, "SMB - LOW", IF([Notification Booked Billing Amount]1 < 3000, "SMB - HIGH", IF([Notification Booked Billing Amount]1 < 5000, "MID Low", IF([Notification Booked Billing Amount]1 < 10000, "Mid High", "Key Account"))))
-
Excellent!
Glad to hear that you solved it!
What was the error?
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
A few formatting issues, specifically with commas inside of the numbers and the $ signs. I think they were unnecessary in the formula but I assumed they had to match exactly what was being populated in the cell but I guess that's not the case!
-
I didn't see it before, but now I do!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Also, I might mention that I changed the formula at the tail end to show if it's greater than 9999 to show "Key Account". Because I had a < for every number except the 10000, when the cell was populated with 10,000 exactly it was showing as blank.
Final Formula:
=IF([Notification Booked Billing Amount]@row = 0, "", IF([Notification Booked Billing Amount]@row < 1000, "SMB - LOW", IF([Notification Booked Billing Amount]@row < 3000, "SMB - HIGH", IF([Notification Booked Billing Amount]@row < 5000, "MID Low", IF([Notification Booked Billing Amount]@row < 10000, "Mid High", IF([Notification Booked Billing Amount]@row > 9999, "Key Account"))))))
-
Nicely done!
Thanks for sharing!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
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