If or Case formula for Calculation

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
  • (Edited)
I am trying to create a formula field with these guidelines:
If or Case  ([Total Points Wk 1] is less than 500, return value "0", 500-999 return value "1", 1000-1499 return value "2", 1500+ return value "3") Once I have a functioning formula field that gives me those 4 results (0,1,2,3) I will have to replicate it for [Total Points for Wk 2], and Wk 3 and 4 and so on... When I try the traditional set up of these functions I get errors for expecting text or number or bool :/
Photo of Michelle Chapman

Michelle Chapman

  • 450 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
You will need to use a normal If() condition versus case based on the fact that you're comparing a range of values. 

Your syntax would be like:

if( 
[Total Points Wk 1]<500,1,
[Total Points Wk 1]<1000,2,
[Total Points Wk 1]<1500,3,
4)

I'm assuming your syntax issue might be because you have your 1,2,3,4 in quotes like "1", "2". 
Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Michelle Chapman

Michelle Chapman

  • 450 Points 250 badge 2x thumb
Perfect! Thank you :)