I have a tricky "OR" formula. Who's up for the challenge? I have a actual vs budget table that I want the variance number to change to red if > 0.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

If([Total Payroll (fixed/variable) VARIANCE] > 0 or[Advertising VARIANCE]or[Compensated Absences (Support) VARIANCE]or[Facilities VARIANCE]or[Fica Forecast - Co. Portion (Support) VARIANCE]or[Health Insurance (support) VARIANCE]or[Other Benefits VARIANCE]or[Other Disc Fringe - 401K (Support) VARIANCE]or[Other Support VARIANCE]or[Payroll & Related VARIANCE]or[Professional Fees VARIANCE]or[Sales Commissions (Support) VARIANCE]or[T&E VARIANCE]or[Total Support VARIANCE]or[Wages - Holiday Earnings (support) VARIANCE], "tomato")If([Total Payroll (fixed/variable) VARIANCE] < 0 or[Advertising VARIANCE]or[Compensated Absences (Support) VARIANCE]or[Facilities VARIANCE]or[Fica Forecast - Co. Portion (Support) VARIANCE]or[Health Insurance (support) VARIANCE]or[Other Benefits VARIANCE]or[Other Disc Fringe - 401K (Support) VARIANCE]or[Other Support VARIANCE]or[Payroll & Related VARIANCE]or[Professional Fees VARIANCE]or[Sales Commissions (Support) VARIANCE]or[T&E VARIANCE]or[Total Support VARIANCE]or[Wages - Holiday Earnings (support) VARIANCE], "green") 

Photo of Joy

Joy

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
First - So that your formula is a bit easier to manage/read (now and in the future), you may want to break it up into multiple formulas or use variables for the criteria. For example, for each color you wish to utilize, you could have a Formula Checkbox that returns True/Checked if the identified criteria is matched. You could then use those fields in a much more simplified formula.


Second - I would setup the formulas as follows:

  • Bad Color: Formula Checkbox


[Total Payroll (fixed/variable) VARIANCE] > 0 or 





[Total Payroll (fixed/variable) VARIANCE] > [Advertising VARIANCE] or 




ETC.


  • Good Color: Formula Checkbox


  • [Total Payroll (fixed/variable) VARIANCE] < 0 or 








    [Total Payroll (fixed/variable) VARIANCE] < [Advertising VARIANCE] or 










    ETC.
  • Variance Value: Formula Text (w/ Allow HTML selected)


  • "<span style=\"color:" & IF([Bad Color],"Red",IF([Good Color],"Green","Black")) & "\">" & [Variance] & "</span>"