Excel Data Types

  DRiM 18:56 19 Apr 2003

I am using a research questionnaire with 100 questions, each having a 'YES', 'NO' or 'Don't Know' response and am trying to use Excel to calculate the results.

Can I assign a key for each of the 'YES' 'NO' and 'Don't know' responses, and is there a way to ask Excel to calculate how many 'Yes' 'No' or 'Don't know' responses appear in a cells in a column or row by adding them up?


  MAJ 20:13 19 Apr 2003

I'll leave the first part of your question to VoG™ but for the second part use the formula:


were A1:A100 is the range and the results you want to count are the yeses:


for the nos

=COUNTIF(A1:A100,"don't know")

for the don't knows.

  DRiM 20:39 19 Apr 2003

Thank you so much, I will try this.

What is VoG?

  MAJ 20:50 19 Apr 2003

What indeed, DRiM, that's a question that has eluded medical science for eons. Seriously though, he's our resident Excel genius, there are others too but VoG™ is the most regular (must be all the muesli).

The first part of your question can probably be solved using a macro, but my macros aren't recording at the minute, so I can't experiment.

  DRiM 20:52 19 Apr 2003

Thanks a lot, you're quite brilliant, it works.

Who or what is VoG?


  DRiM 20:53 19 Apr 2003


hopefully VoG can help with the other bit of the question.

I really am grateful


  MAJ 22:00 19 Apr 2003

Just wondering, DRiM, do you want to be able to press a key and the word Yes, No or Don't know is inserted into whichever cell you are clicked into?

  tbh72 01:14 20 Apr 2003

Sub Yes()
' Yes Macro
' Macro recorded 20/04/2003
' Keyboard Shortcut: Ctrl+y
ActiveCell.FormulaR1C1 = "Yes"
End Sub


Sub No()
' Yes Macro
' Macro recorded 20/04/2003
' Keyboard Shortcut: Ctrl+n
ActiveCell.FormulaR1C1 = "No"
End Sub


Sub Dknow()
' Yes Macro
' Macro recorded 20/04/2003
' Keyboard Shortcut: Ctrl+d
ActiveCell.FormulaR1C1 = "Don't Know"
End Sub


Above shows simple script for assigning either CTRL+y = Yes, CTRL+n = No or CTRL+d = Dont Know, to the active cell, however the line that reads Range("A2").Select needs to be removed to allow for manual navigation to the next question.

  tbh72 01:34 20 Apr 2003

It work's but...... There's alway's a but, I would use the listbox from the FORMS menu. If your having trouble formating the controls shout & I'll send you an example!!!!

  MAJ 13:45 20 Apr 2003

Here’s how I do it in Excel, but bear in mind I Know little about Excel and there’s bound to be better ways, like the code above, I tried pasting it in but it didn’t work for me.

1. Open Excel and click in a cell (it doesn’t matter which cell).

2. Go to Tools > Macro > Record New Macro.

3. In the “Record New Macro” box, type an apt name for your macro (no spaces in the name) call it InsertYes for example.

4. In the “Shortcut Key” box, enter the letter y (lowercase). Click OK. Shortcut will be Ctrl + y.

5. A little box will appear on the worksheet. Type the word YES, which will appear in the cell you clicked on at point 1 above.

6. When YES is entered into that cell, press the down arrow key on your keyboard to move out of the selected cell, then click on the stop button in that little box on the worksheet.

7. Now go to Tools > Macro > Macros and click the Edit button, delete the line “Range("A8").Select” (or similar) and go to File > Close and Return to Excel.

Now when you click in a cell, hold down the Ctrl key and press the y key, YES will appear in that cell. Create two more Macros in the same way for NO and DON’T KNOW, using Ctrl + n and Ctrl + d, respectively.

But tbh72's listbox will probably work better.

  VoG™ 15:40 20 Apr 2003

I don't know a better way than MAJ or tbh72 have suggested.

But, why not simply enter "y", "n" or "d". Once you've filled out your questionnaire, highlight the range of cells, Edit/Replace and replace all "y" with "Yes" and so on.

By the way, MAJ is prone to exaggeration - "genius" I don't think!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?