Help with nested IF statement in conditional formatting

[Anonymous]

Hello!

We have a table that shows various items from our P&L for the current period, average prior 3 periods, and a column with the variance between the two.

We have sales P&L items with a credit balance (looks like a negative) and expenses with a debit balance (looks like a positive), but then we also have gallons sold (looks positive).

If the variance in sales & expenses are  negative, I want them to highlight green and red if the variance is positive.

If the variance in gallons are negative, I want them to highlight red and highlight green if the variance is positive.

I have grouped the GL accounts into [GL Type] = 'Income Expense' or 'Gallons' in order to try and get the conditional formatting to work.

Please see table below for a simplified example:

Category	Current Month	Average 3 Periods	Variance from Average	Color Highlight
Income	-$1,000	-$750	-$250	Green - RGB(198,224,180)
Income	-$1,000	-$1,250	$250	Red - RGB(242,220,219)
Expense	$1,000	$750	$250	Red - RGB(242,220,219)
Expense	$1,000	$1,250	-$250	Green - RGB(198,224,180)
Gallons	$1,000	$750	$250	Green - RGB(198,224,180)
Gallons	$1,000	$1,250	-$250	Red - RGB(242,220,219)

Here is the formula for conditional formatting I currently have (that's not working):

If((((Column(1))-(Column(2))<0) and [GL Type]='Income Expense')

RGB(198,224,180),

(IF(((Column(1)))-(Column(2)))>0) and [GL Type]='Income Expense')

RGB(242,220,219),

(IF(((Column(1)))-(Column(2)))<0) and [GL Type]='Gallons')

RGB(242,220,219),

(IF(((Column(1)))-(Column(2)))>0) and [GL Type]='Gallons')

RGB(198,224,180),

White()

Any suggestions?

[luismadriz · Accepted solution]

HI,

you could use this:

If(Column(1)-Column(2)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(1)-Column(2)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(1)-Column(2)<0 and Category='Gallons',RGB(242,220,219),

If(Column(1)-Column(2)>0 and Category='Gallons',RGB(198,224,180),White()))))

but if Column(3) already has the difference then you could use it:

If(Column(3)<0 and Category<>'Gallons',RGB(198,224,180),

If(Column(3)>0 and Category<>'Gallons',RGB(242,220,219),

If(Column(3)<0 and Category='Gallons',RGB(242,220,219),

If(Column(3)>0 and Category='Gallons',RGB(198,224,180),White()))))

I hope this helps,

Cheers,

Luis