Formula to return a value if values are within a certain range

[carolinem (Caroline McKinnon)]

Hi all

I am trying to get this formula to work out the deal size tier based on the deal value. If it’s within the range of $15,000 to $25,000, it is “extra-small” tier, if it’s within the range of $25,000 - $50,000, it’s “small” tier, etc etc.

I cannot for the life of me get this formula to work. I’m sure it’s some small syntax error. Can someone help? Thanks in advance!

IF(AND({USD Input Deal Value}>15000,{USD Input Deal Value}<25000),“Extra-small”), IF(AND({USD Input Deal Value}>25000,{USD Input Deal Value}<50000),“Small”), IF(AND({USD Input Deal Value}>50000,{USD Input Deal Value}<100000),“Medium”), IF(AND({USD Input Deal Value}>100,000,{USD Input Deal Value}<200000),“Large”), IF(AND({USD Input Deal Value}>200000,“Extra-large”))

[JCorrell (Jim - The Monday Man)]

@carolinem

Try this:

IF(AND({USD Input Deal Value}>15000,
  {USD Input Deal Value}<25000),
  "Extra-small","")&
IF(AND({USD Input Deal Value}>25000,
  {USD Input Deal Value}<50000),
  "Small","")& 
IF(AND({USD Input Deal Value}>50000,
  {USD Input Deal Value}<100000),
  "Medium","")& 
IF(AND({USD Input Deal Value}>100000,
  {USD Input Deal Value}<200000),
  "Large","") &
IF(AND({USD Input Deal Value}>200000),"Extra-large","")

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you?
We Create Custom Solutions
Schedule a 1-on-1 Tutorial Session (for monday, Make & Best Dinosaurs)

[carolinem (Caroline McKinnon)]

Hi Jim! Thanks for getting back to me.

So, that didn’t work either, HOWEVER after an entire nine hours of fiddling, I have finally worked it out!

Turns out that you open the bracket at the start of each IF statement, but you only close them all at the end. This is not required with the SWITCH function, so I didn’t realise it was necessary.

The solution was:

IF(AND({USD Input Deal Value}>=15000,{USD Input Deal Value}<25000),“Extra-small”,IF(AND({USD Input Deal Value}>=25000,{USD Input Deal Value}<50000),“Small”,IF(AND({USD Input Deal Value}>=50000,{USD Input Deal Value}<100000),“Medium”,IF(AND({USD Input Deal Value}>=100000,{USD Input Deal Value}<200000),“Large”,IF({USD Input Deal Value}>=200000,“Extra-large”,IF({USD Input Deal Value}<15000,“N/A”))))))