How to: Format Cell Values that are Above or Below the Average
- 3 minutes to read
This example demonstrates how to apply an "above or below average" conditional formatting rule to a range of cells.
To create a new conditional formatting rule represented by the AverageConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddAverageConditionalFormatting method. Pass the following parameters:
- A CellRange object that defines a range of cells to which the rule is applied.
- A condition specified by one of the ConditionalFormattingAverageCondition enumeration values.
If you use the overloaded method with three parameters, pass the third parameter:
- An integer value representing a number of standard deviations to be included above or below the mean in a range of cells.
- Specify formatting options to be applied to cells if the condition is true using the ISupportsFormatting.Formatting property of the AverageConditionalFormatting object. Set the background color and specify the font attributes.
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-apply-conditional-formatting-to-a-range-of-cells-wpf-spreadsheet-e4968.
Dim conditionalFormattings As ConditionalFormattingCollection = worksheet.ConditionalFormattings ' Create the rule highlighting values that are above the average in cells C2 through C15. Dim cfRule1 As AverageConditionalFormatting = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range("$C$2:$C$15"), ConditionalFormattingAverageCondition.AboveOrEqual) ' Specify formatting options to be applied to cells if the condition is true. ' Set the background color to yellow. cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, &HFA, &HF7, &HAA) ' Set the font color to red. cfRule1.Formatting.Font.Color = Color.Red ' Create the rule highlighting values that are one standard deviation below the mean in cells D2 through D15. Dim cfRule2 As AverageConditionalFormatting = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range("$D$2:$D$15"), ConditionalFormattingAverageCondition.BelowOrEqual, 1) ' Specify formatting options to be applied to cells if the conditions is true. ' Set the background color to light-green. cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, &H9F, &HFB, &H69) ' Set the font color to blue-violet. cfRule2.Formatting.Font.Color = Color.BlueViolet