Need some help

[SallyHBowman]

We are trying to finish up a project and submit it, but none of us are very proficient at Excel. Well, actually -- we were proficient enough to set up a survey, using formulas, which enables us to tally the results of the survey based on responses. After getting it all set up, we were asked to add "n/a" to the "yes" or "no" response choices for all yes/no questions. We figured out how to add the N/A as an option. However, now we need it to count the actual N/A responses and not include those in our percentages of yes/no answers.

It may make more sense if I could somehow show you the document. Is it possible to upload the file to this forum for help? I would appreciate any help!! I have submitted a request for a Mr. Excel Consultant to help me - does that usually take a while to hear back from them? I need help fast! Any suggestions? We used Excel 2007 to complete the survey.

 

[vicedo]

what range has the N/A?

 

[SallyHBowman]

I'm so sorry. I am unsure what you are asking me. On the survey, cells D10 through V69 contain yes/no/n/a choices for responses. Then the worksheets are set up to look for the different responses and provide a percentage of how many are "compliant" or "yes". They do not want the N/A responses to be included in the percentage of compliant answers. Does that helpl?

 

[Alan_P]

Hi Sally,

I'm not sure if it's as simple as this? I might be missing something...

	A	B
1	=COUNTIF($D$10:$V$69,"no")	=A1/($A$1+$A$2)*100
2	=COUNTIF($D$10:$V$69,"yes")	=A2/($A$1+$A$2)*100

<tbody>
</tbody>

Hope this helps,
Cheers,
Alan,

 

[vicedo]

how about you upload a sample of the file to google drive. say like 4 rows for confidentiality reasons and paste the link here

 

[SallyHBowman]

Yes, who can I upload it to? I will upload the file -- it is not confidential -- and not very long of a document. But it has no identifying data on anyone or anything. Some of the rows have yes, no or n/a chosen. I was just filling in a couple to work with the formula. That is not actual data.

 

[SallyHBowman]

Okay -- will upload the link to access it. Thanks. Will just take me a minute.

 

[SallyHBowman]

https://drive.google.com/file/d/0B9lqfn0NA-oEVkE5dWNFZmVfODg/view?usp=sharing

Okay -- this is the document. I added the N/A option to the dropdown menu so they can choose n/a also.

The calculations for the first page are okay. This covers questions II-1 through II-9 --those are fine and do not need changed. However the remaining questions, covering III-1 through VIII-II, need to simply NOT include the n/a responses in the percentages. For instance, to make it easy, if you go the very last question, VIII-II and fill in 4 yes, 4 nos, and 2 n/as. Instead of showing a 60% compliance rate, it should show a 50% (4 yes and 4 no's out of 8 responses, not 4 yes's and 4 no's out of 10 responses). Does that make sense?

 

[SallyHBowman]

I removed all the fake answers I filled in to test the calculations. BTW. Thank you for any help anyone can give me. I'm at my wits end....

 

[SallyHBowman]

Is the link working? Try this link:

https://drive.google.com/file/d/0B9lqfn0NA-oEVkE5dWNFZmVfODg/view?usp=sharing