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?

Andree_Stara

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