This article demonstrates how to highlight the values below average or above average, and how to calculate the number of these values respectively using Spire.XLS for Java.
Sample Document
import com.spire.xls.*; import com.spire.xls.core.IConditionalFormat; import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats; import java.awt.*; public class HighlightBelowAboveAverage { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load a sample Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Use conditional formatting to highlight the values below average in the range "B2:B9" XlsConditionalFormats format1 = sheet.getConditionalFormats().add(); format1.addRange(sheet.getCellRange("B2:B9")); IConditionalFormat cf1 = format1.addAverageCondition(AverageType.Below); cf1.setBackColor(Color.red); //Use conditional formatting to highlight the values above average in the range "B2:B9" XlsConditionalFormats format2 = sheet.getConditionalFormats().add(); format2.addRange(sheet.getCellRange("B2:B9")); IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above); cf2.setBackColor(Color.yellow); //Get the count of values below average sheet.getCellRange("D13").setFormula("=COUNTIF(B2:B9,\"<\"&AVERAGE(B2:B9))"); //Get the count of values above average sheet.getCellRange("D14").setFormula("=COUNTIF(B2:B9,\">\"&AVERAGE(B2:B9))"); //Save the file workbook.saveToFile("BolowOrAboveAverage.xlsx", ExcelVersion.Version2016); } }
Output