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.

 

[SallyHBowman]

I did as you suggested and uploaded a link. Can you please take a look at it and tell me what you think?

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

I removed all the fake data that I had entered just to test the calculations. The calculations for the first page (questions II-1 through II-9) are fine and do not need changed. However the remaining questions, covering III-1 through VIII-2, 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]

Dear Alan -- I am not sure -- it may work to insert that into what I have. I posted a link to my document on Google Drive as another man had suggested. He is not online any longer. Would you be so kind as to help me by looking at my document? I do not have much time left to figure out my dilemma.

 

[Alan_P]

Hi Sally,

Sorry for the delay, I could not access Google Drive in work so had to wait till I got home...

Have a look at the file and see if it's what your after. I've only amended sheet "Numerical Conversion -2 "

I deleted all the helper columns as they were not needed, but if you need them for another reason they can be added back in.

https://drive.google.com/file/d/0B867a8TcsKV6VjBrNFUxMk9nWUE/view?usp=sharing

Oh and your data validation source for "Yes" on sheet "Functions" had a space after it ("Yes ")... took me a while to spot that! It was messing up the formula, just something to be aware of in future...

Hope this helps,
cheers,
Alan.

 

[Alan_P]

Just a mention... the formulas in Columns B & C are array formulas, so if you need to amend them you must press Ctrl + Shift + Enter after the amendment

Also on sheet "Part 2 - Sample" Cell A21 & A22 are both III-12... assume A22 should be III-13?

 

[SallyHBowman]

Thank you, thank you! I did not see that extra space! That was not helping things! You have done exactly what I was looking for! You are THE BEST! I can take it from here. Have a wonderful day!!!!

 

[Alan_P]

No Prob, happy to help Glad it worked for you!