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

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

@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","")
``````

1 Like

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.