Between function

[emilyapril]

Hi!

I have a table which uses the following expression:

=sum([Last Month Forecast]-[Current Forecast])

This will give me a negative number or positive number.
Id like to format the background of each cell depending on the number.
I cannot use visual cues as I have THREE ranges Id like to work with. One of these ranges, being between 1 & 60.

I had worked out the expression for a basic, two part "if statement" but cannot find the right way to put "between" in the expression:

example- (which with a calculation in place of the underscores, is Ok. )

sum(
if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),
if([Last Month Forecast]-[Current Forecast]_________,RGB(0,0,255)
))))

so where i have the ___ I need to say between 1 & 60..

many thanks in advance!

[MindaugasBacius · Accepted solution]

You just do not need the last If statement 🙂

if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),RGB(0,0,255)))

Add this expression to:

This should help you solve the issue.

[lockematthewp · Accepted solution]

sum(
if([Last Month Forecast]-[Current Forecast]<=0,RGB(255,0,0),
if([Last Month Forecast]-[Current Forecast]>=61,RGB(0,255,0),
if(([Last Month Forecast]-[Current Forecast]> 0) and ([Last Month Forecast]-[Current Forecast] < 61),RGB(0,0,255)
))))