Skip to main content
Nintex Community Menu Bar

Calculated Value with Complex If Formula Statement

  • October 7, 2020
  • 3 replies
  • 7 views
Forum|alt.badge.img+1

Hello!

 

I'm working on an Audit form and am trying to create a calculated value that updates based on the audit score being calculated. I have tried writing the formula every which way I know how, but am unable to get it to update past the first If/then statement.

 

Here is what I'm trying to say:

If CallAuditScore < 95, "Needs Improvement", If CallAuditScore is between 95 & 100, "Successfully Meets", If CallAuditScore is between 100.01 & 115, "Exceeds", If CallAuditScore is > 115, "Exceptional"

 

Here are some examples of what I have tried:

If(lessThan(CallAuditScore, 95), "Needs Improvement", If(or(greaterThanOrEqual(CallAuditScore, 95), lessThanOrEqual(CallAuditScore, 100)), "Successfully Meets", If(or(greaterThan(CallAuditScore, 100), lessThanOrEqual(CallAuditScore, 115)), "Exceeds", If(greaterThan(CallAuditScore, 115), "Exceptional", " "))))

If(CallAuditScore<"95","Needs Improvement", If(CallAuditScore>="95" || CallAuditScore<="100","Successfully Meets", If(CallAuditScore>"100" || CallAuditScore<="115","Exceeds", If(CallAuditScore>"115","Exceptional","NA"))))

If(CallAuditScore<"95","Needs Improvement"," ") ||
If(or(CallAuditScore>="95", CallAuditScore<="100"),"Successfully Meets"," ") ||
If(or(CallAuditScore>"100", CallAuditScore<="115"),"Exceeds"," ") ||
If(CallAuditScore>"115","Exceptional"," ")

If(CallAuditScore<"95","Needs Improvement", If(CallAuditScore>="95","Successfully Meets", If(CallAuditScore>"100","Exceeds", If(CallAuditScore>"115","Exceptional"," "))))

 

I've scoured posts in the forum and have come up blank. I've tried adding one piece at a time and I get closer, but the minute I add an additional If statement, it stops calculating.

 

The most recent I've tried is below:

If(and(CallAuditScore>="95", CallAuditScore<="100"),"Successfully Meets", If(and(CallAuditScore>="101", CallAuditScore<="115"),"Exceeds","Needs Improvement"))

    3 replies

    Forum|alt.badge.img+1
    • Khymburlie
    • Author
    • October 7, 2020
    As an update, the current formula I have is below:

    If(and(CallAuditScore>="95", CallAuditScore<="100"),"Successfully Meets", If(and(CallAuditScore>="101", CallAuditScore<="115"),"Exceeds", If(and(CallAuditScore>="116", CallAuditScore<="135"),"Exceptional","Needs Improvement")))

    Everything works except for the 95-100 range reflects "Needs Improvement"
    Simon Muntz
    Nintex Partner
    Forum|alt.badge.img+23
    • Simon MuntzNintex Partner
    • Nintex Partner
    • October 8, 2020

    Hi,

     

    I used the following formula and the form behaved as expected.

    If(and(greaterThanOrEqual(score,95),lessThanOrEqual(score,100)),"Meets",If(and(greaterThanOrEqual(score,101),lessThanOrEqual(score,115)),"Exceeds","Improvement"))

     

    Attached a responsive form with the POC.

     

    We provide Nintex Training & Consulting - https://www.definiti.com.au/
    Forum|alt.badge.img+1
    • Khymburlie
    • Author
    • October 8, 2020

    Thanks, @SimonMuntz. That formula is actually missing the last condition, but basically ended up going a similar route and was able to get it to work

     

    I know initially I should have been using "And" statements rather than "Or", but once I made that change, I'm still not sure why my corrected one didn't work, but in case anyone is looking for something similar, here is what worked:

     

    If(lessThan(CallAuditScore, 95), "Needs Improvement", If(and(greaterThanOrEqual(CallAuditScore, 95), lessThanOrEqual(CallAuditScore, 100)), "Successfully Meets", If(and(greaterThan(CallAuditScore, 100), lessThanOrEqual(CallAuditScore, 115)), "Exceeds", If(greaterThan(CallAuditScore, 115), "Exceptional", " "))))
    Sidebar Buttons
    Nintex Community Home Contact Nintex Support Email Nintex Community Check Nintex Status
    Terms & ConditionsAccessibility statement