AblebitsAvgByColor function

The AblebitsAvgByColor function returns the average (arithmetic mean) of numbers in cells with the specified in the argument background color.

For example:

  • =AblebitsAvgByColor(C3:I9,N22) Returns the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the same background color as the N22 cell.
  • =AblebitsAvgByColor(C3:I9,255) Returns the average (arithmetic mean) of numbers in a range of cells C3:I9 that have the background color of 255 color code.
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

AblebitsAvgByColor(range,color)

The AblebitsAvgByColor 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 background color of cells to calculate the average value. Can be a reference to one cell like B6 or color code like 255.
Note. If a cell range in the first argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Remarks

  • To include logical values and text representations of numbers in a reference as part of the calculation, use the AblebitsAvgAByColor function.
  • The AblebitsAvgByColor 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 background color of a cell, you can use the AblebitsGetCellColor 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.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.