Help needed with different data types

[Nemesis247]

Hi all

I'm new to this forum and would appreciate some help with a problem I'm having.

The scenario is that I am trying to aggregate survey responses for the year from separate spreadsheets to quarterly and yearly totals. The responses contain not only numerical values but contain, '*', 'N/A' and blank cells depending upon the return. For transparency, I do not want to amend the return in any way.

I am running VLOOKUPS to bring in the 'Percentage Not Recommended' figure, for example, for Jan, Feb and March into 'QTR 1' and working out the overall percentage. So for QTR 1, I'm using the formula =IFERROR(SUM(F3:H3)/COUNTIF(F3:H3,">0"),0) which is the sum of the percentages divided by the number of returns for that quarter, but run into an issue where I get '0' if the return is not a numerical value.

I would like '0' to be returned if the percentage truly is '0' but something like 'no data' if the cells contain no numerical values or are blank. I haven't figured out a way to get this working yet - any assistance would be really appreciated.

Ken

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Nemesis247]

Hi AliGW

Thanks for your response. I've uploaded the sample spreadsheet as requested.

Regards

[6StringJazzer]

Try this formula in E2 then copy to E3:E4 and I2:I4.

=IF(COUNT(B2:D2)=0,"No data",AVERAGE(B2:D2))

[Nemesis247]

Hi Jeff

Just tried it and it worked a treat, thank you so much for this! I had it in my head that I would have to deal with one or another of the excel handle errors to get this working. I spend hours fiddling with them to no avail. Hats off to you mate. Thanks also to AliGW for your response.

Regards

Ken

[6StringJazzer]

Glad it helped. Come on back if you have other problems!