1 Reply Latest reply: Jan 5, 2018 11:08 PM by Luis Madriz RSS

    Help with nested IF statement in conditional formatting

    Steph Bass



      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:

      CategoryCurrent MonthAverage 3 PeriodsVariance from AverageColor Highlight


      -$1,000-$750-$250Green - RGB(198,224,180)
      Income-$1,000-$1,250$250Red - RGB(242,220,219)
      Expense$1,000$750$250Red - RGB(242,220,219)
      Expense$1,000$1,250-$250Green - RGB(198,224,180)
      Gallons$1,000$750$250Green - RGB(198,224,180)
      Gallons$1,000$1,250-$250Red - 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')


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


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


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





      Any suggestions?

        • Re: Help with nested IF statement in conditional formatting
          Luis Madriz





          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,