Removing #DIV/0! using Average IF ERROR

[cpmays]

I have a list of values from a lookup and I am trying to get the average across the row which is working fine. However, if someone doesn't rate any questions and they are all blanks, rather than the #DIV/0! I need it to be a zero due to Power BI tripping on this when refreshing. I can't remove the row as it's pulling from SharePoint and it'll just refresh the blanks.

The formula I am using is:

=AVERAGE(IF(ISERROR(A4:X4),"",A4:X4))

Any suggestions?

 

[cpmays]

Where are these 500 pre-populated cells?
Note that if you are trying to autofill formulas in column A, it will only go as far down as there are continuous populated rows in column B.
In your posted example, I don't see column B populated after cell B71, so it appears that it is behaving exactly as expected.

The information is coming from the "owssvr" sheet noted in the VLOOKUP formula.

Once I went to row 71 and just dragged the formula down and it populated the cells. I dragged it down to 1000 to when the owssvr sheet updates from SharePoint it will update this sheet.

 

[Joe4]

The information is coming from the "owssvr" sheet noted in the VLOOKUP formula.

Once I went to row 71 and just dragged the formula down and it populated the cells. I dragged it down to 1000 to when the owssvr sheet updates from SharePoint it will update this sheet.

Note that FillDown only looks at the populated cells on the adjacent columns in the current sheet.
It does not FillDown based on data found on other sheets.