# Using Excel AVERAGEIF in a Calculated field

Using this statement =AVERAGEIF(B2:B13,"<>0") found here: https://www.extendoffice.com/documents/excel/1573-excel-average-ignore-zero.html

My calculated SharePoint field would be this: =AVERAGEIF(VALUE([Repeat 1]),VALUE([Repeat 2]),"<>0")

I'm converting a text field to numbers. It works perfectly when I use the AVERAGE command, but it includes 0 values.

The AVERAGEIF statement is the correct syntax. At least I think it is. Any help, as always would be appreciated.

AVERAGEIF is not supported in SharePoint Calculated Columns.

Here's a list of supported functions: (scroll down to "Alphabetical list of functions")

https://support.office.com/en-us/article/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3

A possible solution looks like this:

``````=SUM(
IF( ISNUMBER( VALUE(Column1) ), VALUE(Column1), 0 ),
IF( ISNUMBER( VALUE(Column2) ), VALUE(Column2), 0 ),
IF( ISNUMBER( VALUE(Column3) ), VALUE(Column3), 0 ),
IF( ISNUMBER( VALUE(Column4) ), VALUE(Column4), 0 ),
IF( ISNUMBER( VALUE(Column5) ), VALUE(Column5), 0 ),
IF( ISNUMBER( VALUE(Column6) ), VALUE(Column6), 0 ) )
/
(
IF( ISERROR(Column1 / 1), 0, 1 ) +
IF( ISERROR(Column2 / 1), 0, 1 ) +
IF( ISERROR(Column3 / 1), 0, 1 ) +
IF( ISERROR(Column4 / 1), 0, 1 ) +
IF( ISERROR(Column5 / 1), 0, 1 ) +
IF( ISERROR(Column6 / 1), 0, 1 )
)
``````

The formula will covert the text into numbers, if possible, and then calculate the average by taking the sum of the numbers and dividing by the count of the numbers.

• Thanks Mike, I used your syntax, and nothing broke, but for a list item that has the first 8 columns populated the average field still averages all 16 fields. Here's my formula based on your example. (Sorry about the formatting. I omitted some fields in my example.) =SUM( IF( ISNUMBER( VALUE([Repeat 1]) ), VALUE([Repeat 1]), 0 ), IF( ISNUMBER( VALUE([Repeat 2]) ), VALUE([Repeat 2]), 0 ), IF( ISNUMBER( VALUE([Repeat 16]) ), VALUE([Repeat 16]), 0 ) ) / ( IF( ISERROR([Repeat 1] / 1), 0, 1 ) + IF( ISERROR([Repeat 2] / 1), 0, 1 ) + IF( ISERROR([Repeat 16] / 1), 0, 1 ) ) – Steve Morley Apr 3 '19 at 10:04