Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.

AblebitsAvgAByFontColor function

The AblebitsAvgAByFontColor function calculates the average (arithmetic mean) of numbers in cells with the specified font color. Can include empty cells or not depending on the argument.

For example:

  • =AblebitsAvgAByFontColor(C3:I9,N22) Calculates the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the same font color as the N22 cell. Blank cells are not included into the calculation.
  • =AblebitsAvgAByFontColor(C3:I9,N22,FALSE) Calculates the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the same font color as the N22 cell. Blank cells are included.
  • =AblebitsAvgAByFontColor(C3:I9,255) Calculates the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the font color of 255 color code. Blank cells are not included into the calculation.
  • =AblebitsAvgAByFontColor(C3:I9,255,FALSE) Calculates the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the font color of 255 color code. Blank cells are included.
Note. Formulas created with this function will work only on machines with installed Ultimate Suite for Excel. If the workbook is opened on another computer where Ultimate Suite is not installed, the result will be lost.
Tip. You can find the complete list of custom functions on the Ablebits functions for Excel page.

Syntax

AblebitsAvgAByFontColor(range,color,skip_empty_cells)

The AblebitsAvgAByFontColor function syntax has the following arguments:

  • range
    Required. The range of cells where you want to find the average value, e.g. B2:B8.
  • color
    Required. The font color of cells to calculate the average value. Can be a reference to one cell like B6 or color code like 255.

  • skip_empty_cells
    Optional: TRUE or FALSE. If you do not add this argument, the result will equal to TRUE and will not include empty cells into the calculation. However, if you add the FALSE argument, empty cells will be included.
Note. The AblebitsAvgAByFontColor function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the AblebitsAvgAByFontColor function counts that value.

Remarks

  • The AblebitsAvgAByFontColor function is used by the Count and Sum by Color add-in when you check the Insert result as formula box before pasting the result.
  • If you want to get the color code of the font color of a cell, you can use the AblebitsGetCellFontColor function.
  • Multiple ranges are not supported.
  • The function does not work for conditionally formatted cells and for ranges formatted as tables with colored alternate rows or columns.