IF Statement for a range of numbers

Jessb9187

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?

Nic Larsen

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

 

Jessb9187

Hey Nic,

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

Andrée Starå

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

Jessb9187

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

 

 

Andrée Starå

Excellent!

Glad to hear that you solved it!

What was the error?

Best,

Andrée

Jessb9187

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!

 

Andrée Starå

I didn't see it before, but now I do! 

Jessb9187

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

Andrée Starå

Nicely done!

Thanks for sharing!

Best,

Andrée