if condition with range of values

[rtr13]

Hi All,

I am trying to set certain colors to values and am having troubles with my if statement. Currently, this is what I have as my if statement to determine colors:

=if(change_percent >= 0 ,green(),
     if(change_percent > (-5),rgb(255,0,0),
         if(change_percent > 0 and change_percent <= -5,rgb(255,205,3))))

Currently, the range of values in this case between 0 and -5, isn't calculating properly. Does anyone know what I am doing wrong?

Thanks,

Rose

[dplr-rn]

=if(change_percent >= 0 ,green(),
if(change_percent > (-5),rgb(255,0,0), did you mean change_percent < -5? because > -5 would mean between -5 and 0
if(change_percent > 0 and change_percent <= -5,rgb(255,205,3))))  assuming this is part you want between -5 and 0. it should be change_percent < 0 and change_percent >= -5

[rtr13]

Unfortunately that didn't work... I am looking for values above 0 to be green, between negative 5 and 0 to be yellow and anything lower than -5 should be red. 

Thanks!

Rose

[dplr-rn]

did you try like below?

=if(change_percent >= 0 ,green(),
if(change_percent < (-5),rgb(255,0,0), 
if(change_percent < 0 and change_percent >= -5,rgb(255,205,3))))

[Brett_Bleess]

Rose, attaching a sample app on this one will be the best way to get a working solution, this way folks can dig into things to try to figure out why the expression is not working, which would likely have to do with the data model etc.

Regards,
Brett

[rtr13]

Hello,

Thank you for your responses! I attached a screenshot of how my data is formatted in the data model. In addition, below is the expression I put into my text color section of the expressions. 

=if(percentchange >= 0 ,green(),
     if(change_percent > -5,rgb(255,0,0),
          if(percentchange < 0 and percentchange >= -5,rgb(255,205,3))))

I also included a screenshot of the values in my table. I formatted the pivot table to have the columns as rows (just as an FYI)! I also formatted the values into percentages in my table, could this be a reason why the expression won't work? 

Sorry for the delay in responses, I appreciate the help!

[ManimekalaiS]

=if(change_percent >= 0 ,green(),
if(change_percent < 0 and change_percent >= -5,yellow(),
if(change_percent < 0 and change_percent < -5,red()))) // Here ii) <0 and >-= -5 falls (-1,-2,-3,-4). iii) <0 and <-5 falls(-6,-7,-8....)