Excel Data Types

  DRiM 18:56 19 Apr 03
Locked

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?

DRiM

  MAJ 20:13 19 Apr 03

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

=COUNTIF(A1:A100,"yes")

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

=COUNTIF(A1:A100,"no")

for the nos

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

for the don't knows.

  DRiM 20:39 19 Apr 03

Thank you so much, I will try this.

What is VoG?

  MAJ 20:50 19 Apr 03

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 03

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

Who or what is VoG?

DRiM

  DRiM 20:53 19 Apr 03

Thanks,

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

I really am grateful

DRiM

  MAJ 22:00 19 Apr 03

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 03

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

------------------------------

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

------------------------------

Sub Dknow()
'
' Yes Macro
' Macro recorded 20/04/2003
'
' Keyboard Shortcut: Ctrl+d
'
ActiveCell.FormulaR1C1 = "Don't Know"
Range("A2").Select
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 03

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 03

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 03

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.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

iPhone 7 review: Enhanced cameras, a refreshed design and water resistance make the iPhone 7 an…