0

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.

0

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 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.