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]

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,"")

That formula works just like the existiing one and still gives me the #VALUE! error in some cells that have a larger amout of text...?

 

[Joe4]

That formula works just like the existiing one and still gives me the #VALUE! error in some cells that have a larger amout of text...?

It seemed to work fine for me on the last example you posted.

Can you post data from an actual example where my formula does NOT work?

 

[cpmays]

It seemed to work fine for me on the last example you posted.

Can you post data from an actual example where my formula does NOT work?

Here's screenhot of the vlookup sheet with your formual from above. As you can see I am still gettgin the #VALUE! error and then the other two columns (which you helped me with, thank you!) are showing zeros. Keep in mind that all of the blank rows between the numbers are also text, but for some reason if it is a large amont of text only then does it return the #VALUE# error. There are no sepcial chararcters in the text response, only words, but it should be bshowing bank as it is doing in all the other cells where the vlookup sees text.

 

[Joe4]

I really do not like how you have your VLOOKUP formula structured.
The first argument is supposed to be a single cell or value (the value you are trying to look up), not a whole range of values like: J2:AG340.
Also, it appears that the formula you are showing on the screen is in cell A1, yet the first value you are looking up is in cell J2, not J1. That does not seem right to me either.

Assuming that what posted is on the "owssvr" sheet, it seems to me that the formula in cell A1 should be written like:

=IFNA(VLOOKUP(J1,Scale!$A$1:$B$7,2,0)+0,"")

and then copied down column A for all rows.

 

[cpmays]

I really do not like how you have your VLOOKUP formula structured.
The first argument is supposed to be a single cell or value (the value you are trying to look up), not a whole range of values like: J2:AG340.
Also, it appears that the formula you are showing on the screen is in cell A1, yet the first value you are looking up is in cell J2, not J1. That does not seem right to me either.

Assuming that what posted is on the "owssvr" sheet, it seems to me that the formula in cell A1 should be written like:

=IFNA(VLOOKUP(J1,Scale!$A$1:$B$7,2,0)+0,"")

and then copied down column A for all rows.

2017
I understand it looks strange but it’s working.

The owssvr sheet is pulling from SharePoint list and when I refresh the Excel sheet it updates the data from the SharePoint. The this sheet uses that VLOOKUP to convert the text words matching the Scale (Very Satisfied, Satisfied, Somewhat Satisfied, etc.) to a number (0-5).

I am skipping some columns as the first columns until J are mainly questions where the scale I am using won't be relevant. At column J is where the first question I have where the respondent would answer with one of the scale ratings (Very Satisfied, Satisfied, etc.) and then the scale turns that into a value from 0-5, and why not just skip those, right as I am just looking for the answers where the sale would be relevant ? I am starting on J2 and not J1 because the sheet has headers.

While the SharePoint won’t have that many responses (3410) that's probably more than I'll get, but this way the VLOOKUP will look for cells with those words convert them to the numerical value from the scale and I won’t need to update this sheet with more rows with the formula. I could put it 2500 or even 2000, but I put around 3000 just in case. Maybe I am using VLOOKUP all wrong, but for the most part it is working. I just have this issue where if the cell has too much text it will give me that #VALUE! Error, and zero out those two columns that average and % the score. But I only have 8 of those.

If I cannot get the formula to work the workaround would be to remove some of the text in the longer answers people are providing (and save it off somewhere) that are causing the error, and then #VALUE! Errors will disappear. Since it’s only occurring on those cells the VLOOKUP sees where the text answers are too long.

The other workaround would be to revert back to my original formulas for those two columns that are getting zeroed out, but then I have that #DIV! error on cells where there has yet to be refreshed data.

Thanks for all your help!!

 

[Joe4]

I understand it looks strange but it’s working.

Not entirely, or you wouldn't be here, right?

Can you at least try the changes I proposed?

 

[cpmays]

Not entirely, or you wouldn't be here, right?

Can you at least try the changes I proposed?

True.

Yeah of course, let me see...

 

[cpmays]

If I copy and paste this formula into the spreadhseet, nothing happens..

 

[cpmays]

If I copy and paste this formula into the spreadhseet, nothing happens..

View attachment 64361

I see it was missing the reference sheet 'owssvr'. I add that in and change it to J2 since J1 is just a header.

As you can see it is changing the value there to a number from the Scale like it should. If I double click to auto-fill it only goes down 70 cells even when there are over 500 cells populated. Why is this?

I went ahead and auto-filled across the columns and also auto-filled thoes rows, but for some reason it's only going down to row 70. If I go to row 70 and try and auto fill it won't do anyting...

I went ahead an populated the Avg, %, & Score colums, (where I had the #DIV/0! issue in the frist place and you corred the % column formula) but I can see I am still gettign the 0 in the cells where the VLLOUP is retuniung a #value. Again in those cells there is text, hbowever I am only stil lgettgin the #VALUE! error in those cells with more than a few centences. So both our VLOOKUP formulas are doing the sdame thing..

Is there anyway I can send you a sample of this file? I cannot get the Mini-sheet to work.

 

[Joe4]

As you can see it is changing the value there to a number from the Scale like it should. If I double click to auto-fill it only goes down 70 cells even when there are over 500 cells populated. Why is this?

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.