IF Statement for a range of numbers

Options
✭✭✭✭
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

I believe you need to remove the "1" in after the column names. You only need the @row following.

=IF([Notification Booked Billing Amount]@row

• ✭✭✭✭
Options

Hey Nic,

That doesn't seem to fix the issue. Any other ideas?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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"))))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
edited 06/05/19
Options

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.

• ✭✭✭✭
edited 06/05/19
Options

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"))))))

• ✭✭✭✭✭✭
Options

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.