Formula needed please

[xxsalahxx]

I have 50 rows of data entered into a spreadsheet the spreadsheet has 19 questions and I wish to sum only certain ones which i have done so by using analysis
=D2+E2+K2+L2+M2+N2+W2, (that was the easy part).

In my next column i want to divide the total number from the questions answered above, However, if any of the questions were answered using a zero or left blank i want to omit them.
*Example maybe only 5 questions were answered.

Thank you in advance for any help I can receive with this.

 

[villy]

Can you provide some example...or could you explain further a little confusing?
Thanks

 

[Ruddles]

Is this the same question as http://www.mrexcel.com/forum/showthread.php?t=565597?

I'm assuming you didn't see VoG's response, since you didn't reply to it.

 

[xxsalahxx]

Hello this is a different question, thank you and thanks to Vog

 

[GlennUK]

It looks like the same question, but just having zeroes omitted as well. Is that right?

 

[xxsalahxx]

Re message below: I am trying to work out a mean, in which From the rows of 7 questions that i have summed up, i wish to divide this by the total number of questions answered in this case i have 7, however some questions were not answered possibly 6 or 5 so i would like to omit them from the analysis. Row 1 on my spreadsheet is q1. q2.q3.etc etc



I have 50 rows of data entered into a spreadsheet the spreadsheet has 19 questions and I wish to sum only certain ones which i have done so by using analysis
=D2+E2+K2+L2+M2+N2+W2, (that was the easy part).

In my next column i want to divide the total number from the questions answered above, However, if any of the questions were answered using a zero or left blank i want to omit them.
*Example maybe only 5 questions were answered.

Thank you in advance for any help I can receive with this.

 

[xxsalahxx]

That might work Glenn!

 

[xxsalahxx]

Going to try to explain this a bit better...
I have 50 rows of data, adn 19 questions, I want to total only certain questions which were questions 1,2,8,9,10,11 and 18, which i done using the formula =D2+E2+K2+L2+M2+N2+W2, this worked alright. In my next column i need to divide the number of these questions answered. So if all 7 were answered i need to divide by 7, however some have only answered 6,5, or 4 questions how do i analyse this? Sorry for any inconvenivce caused or time wasted on this question but your help is much appreciated.

 

[villy]

Can you provide some example sheet? I don't really get to analyze it.
Thanks

 

[GlennUK]

How about an array formula ( entered using Ctrl-Shift-Enter instead of Enter ) of:

=AVERAGE(IF(D2:W2>0,D2:W2))

or, in Excel 2007 and higher:
=AVERAGEIF(D2:W2,">0",D2:W2)