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?

 

[Joe4]

Welcome to the Board!

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

If A4:X4 are all blanks, that formula will return 0, not a #DIV/0 error.

What exactly is in cells A4:X4 that is causing issues?
Are there errors in that range?
If so, what are the errors, and if there are they are the result of formulas, please post the formula that returns the error.

 

[cpmays]

So that row is all blank is it the result of the foolwing vlookup:

=IFNA(VLOOKUP(owssvr!$J$2:$AG$3101,Scale!A1:B7,2,0),"")

It's taking ratings from a survey and loking at a scale to deterine the number 1-5. if a user chosses NA for the rating it appreas as blank. So maybe I should update the scale to include N/A as 0?

The scale is very simple:

Code	Value
Very Unsatisfied	0.00​
Unsatisfied	1.00​
Somewhat Unsatisfied	2.00​
Somewhat Satisfied	3.00​
Satisfied	4.00​
Very Satisfied	5.00​

But since there is free text options mixed in with the ratings I have the vlookup diregard those. But I am not sure why I a getting the #DIV/0! error.

For AA column the formula is:
=SUM(IF(ISERROR(A39:X39),"",A39:X39))

For AB column the formula is:
=AVERAGE(IF(ISERROR(A39:X39),"",A39:X39))

For AC column the formula is:
=IFERROR(AB39/5,0)

Many thanks for all your help!

 

[Joe4]

Try replacing your formula in AB of this:

=AVERAGE(IF(ISERROR(A39:X39),"",A39:X39))

To this:

=IFERROR(AVERAGE(A39:X39),"")

 

[cpmays]

Try replacing your formula in AB of this:

=AVERAGE(IF(ISERROR(A39:X39),"",A39:X39))

To this:

=IFERROR(AVERAGE(A39:X39),"")

It makes it blank, that's better than the error.

Should I change it to:

=IFERROR(AVERAGE(A39:X39),"0")?

 

[Joe4]

It makes it blank, that's better than the error.

Should I change it to:

=IFERROR(AVERAGE(A39:X39),"0")?

Anything enclosed in double-quotes is treated as literal text, and not numbers.
If you want the number 0, then use:

=IFERROR(AVERAGE(A39:X39),0)

I would also recommend using this IFERROR formula on column AA in the same manner.

 

[cpmays]

Anything enclosed in double-quotes is treated as literal text, and not numbers.
If you want the number 0, then use:

=IFERROR(AVERAGE(A39:X39),0)

I would also recommend using this IFERROR formula on column AA in the same manner.

That worked - huge thanks! Also the formulas look more comprehensible.

Why do you recommend changing the AA column?

 

[Joe4]

That worked - huge thanks! Also the formulas look more comprehensible.

You are welcome.

Why do you recommend changing the AA column?

Never mind on that one. I was misinterpretting what you were doing with that.

 

[cpmays]

So this is interesting: these two formulas are retunring a 0 when the text in the row is a certain length, is that possible?

So for exmaple is here is the row it's pulligin from another tab via =IFNA(VLOOKUP(owssvr!$J$2:$AG$3410,Scale!A1:B7,2,0),"")

E​

F​

G​

H​

I

J​

K​

L​

M​

N​

O​

P​

Q

R

S​

T​

U

V​

W​

X​

Y

Z

AA​

AB​

AC​

2​

1​

1​

2​

3​

2​

2​

4​

1​

1​

4​

2​

3​

#VALUE!​

3​

4​

4​

50​

0.00​

0.00%​

The numbers are from the rating scale and the #VALUE is text. Now all the other blank colums are have text as well but there is no #VALUE in those, and for some some of the cells are retuning #VALUE, and the forumals in AB and AC are zeros. If I shorten the text response in the originating sheet, then those formulas work. Is there a limit of what the VLOOKUP ignores?

 

[Joe4]

If the average formula I provided returns zero, it may be because the VLOOKUP formula is actually returning text values (numbers entered as text).
If that is the case, you may be able to coerce them to numbers like this:

=IFNA(VLOOKUP(owssvr!$J$2:$AG$3410,Scale!A1:B7,2,0)+0,"")