Between Ranges Formula

Platform discussions
1

I’m currently trying to create the following range formula in Monday. It works correctly in Excel but not in Monday. Do you wonderful people have any ideas on how I might achieve this.

If less than 1.7 enter Low in the cell, if between or equal to 1.8 and 2.3 then enter Medium, or if the score is between 2.4 and 3 enter High. If the value is greater than 3.1 then rate Very High.

NOTE: I replaced the E2 with {Value Score} in Monday.
=IF(AND(E2>=0, E2<=1.7), “Low”,IF(AND(E2>=1.8, E2<=2.3), “Medium”, IF(AND(E2>=2.4, E2<=3),“High”, IF(AND(E2>=3.1),“Very High”, 0))))

2

Hi @adydent23 the syntax for nested ifs requires an extra parenthesis around each nested IF

IF(AND({Numbers}>0,{Numbers}<=1.7),"Low",(IF(AND({Numbers}>1.7, {Numbers}<=2.3),"Medium",(IF(AND({Numbers}>2.3, {Numbers}<=3),"High",(IF({Numbers}>3,"Very High",0)))))))

Numbers would be your Value Score. Also I changed the logical statements a hair to be a little more precise in case 1.75 value is possible.

Hope that Helps.

3

Thanks @timlittletech your formula works well in Excel, but when I insert within Monday I’m getting the same illegal formula message. :frowning: I really thought it would work too. Adrian

1 Like
4

Hi @JCorrell and @Scott-monday.com

I’ve noticed you’ve responded in the past to Nested formula questions and I’m hoping you can help with this one too. The formula works in Excel, but no in Monday - can you see how it should be adjusted? Or is there an alternative formula I should use?

IF(AND({Value Score}>0.0, {Value Score}<=1.7), “Low”,(IF(AND({Value Score}>1.7, {Value Score}<=3.0), “Medium”, (IF(AND({Value Score}>3.0, {Value Score}<=5.0),“High”, (IF(AND({Value Score}>5.0,“Very High”, 0)))))))

(Biz+Cust+Ops)/EffortComplexity = Value Score

ValueScore1
ValueScore1901×181 8.99 KB

regards
Adrian

5

@adydent23,

I try to avoid nested IFs whenever possible in monday.

In this case, because your conditions are mutually exclusive, they are not needed. Try this…

IF(AND({Value Score}>0,{Value Score}<=1.7),"Low","") & IF(AND({Value Score}>1.7, {Value Score}<=2.3),"Medium","") & IF(AND({Value Score}>2.3, {Value Score}<=3),"High","") & IF({Value Score}>3,"Very High","")

If you have any trouble, add the IFs one at a time to help figure out what’s wrong.

1 Like
6

You Beauty! Nicely done @JCorrell it works perfectly. Anyone reviewing this Nested Formula look out for the last IF which doesn’t need the AND.

Regards

Adrian

1 Like
7

Thanks @JCorrell that solution is much cleaner. Glad it works.

@adydent23 looks like you figured out the syntax of the last if that shouldn’t have the AND statement - looks like from the previous example it was missing a closing parenthesis also

Glad your formula is now working!

1 Like
8

And thanks again for your support Tim, it did help with the refinement.

9

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