9

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][email protected] < $1,000.00, "SMB - LOW", IF((Notification Booked Billing Amount][email protected] < $3,000.00, "SMB - HIGH", IF([Notification Booked Billing Amount][email protected] < $5,000.00, "MID Low", IF([Notification Booked Billing Amount][email protected] < $10,000.00, "MID High", "Key Account")))

What am I missing?

Comments

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

 

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, [email protected])

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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

 

 

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!

 

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