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

    Help with nested IF statement in conditional formatting

    Steph Bass

      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:

      CategoryCurrent MonthAverage 3 PeriodsVariance from AverageColor Highlight

      Income

      -$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')

      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?

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

          HI,

           

          Untitled.png

           

          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