Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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