PDA

View Full Version : Macro for counting frequencies



bluebonnet
01-12-2016, 08:57 AM
Hello,

I would like to create a macro that will count frequencies in a column. For example, if a survey asked respondents to rate their experience as "Excellent", "Good", "Fair" or "Poor", I'd like to run a macro in excel that would give me percentages for each response for that variable. It would be something like Excellent - 50%, Good - 20%, Fair - 10%, Poor - 20%. For an item with Strongly Agree, Agree, Disagree, and Strongly Disagree, I would also like a macro that can combine the frequencies of both Strongly Agree and Agree, so that I could say "40% of respondents either agreed or strongly agreed with the statement".

Thank you in advance for your help!

JKwan
01-12-2016, 09:47 AM
This version contains both counting and splitting

bluebonnet
01-12-2016, 10:21 AM
Thank you so much!!!

Here is a more complicated follow up as I'm working through in my head what would be the most beneficial macro that would help me:

Looking at the spreadsheet you created, can I write a macro that will count the frequencies for each question and then place the frequencies in a different sheet (maybe one I've already created that has the Questions listed). For example, an additional sheet will be called "Frequencies" and then on that sheet it will say: "Question 1" with Excellent, Good, Fair, and Poor listed and then in the boxes next to those, the macro would put the percentage frequencies into the corresponding cell? Then Question 2.. Question 3.. etc.

JKwan
01-12-2016, 10:43 AM
forgot to put in the second portion, here is the updated workbook. This contains all the updates and splitting as well

JKwan
01-12-2016, 10:52 AM
well, anything is possible, you just have to hammer it out. Since, the data is from my head, I don't know if it is valid for your need. If you can send me really data (scrubbed cleaned - no personal data), that may be better. I hate going down the road and then the solution don't fit your problem. I MAKE NO PROMISES to help you entirely! Given your questions are answered.

JKwan
01-12-2016, 10:59 AM
Updated code, this is better:


Sub CalculateSurvey2()
Dim WS As Worksheet
Dim lLastRow As Long
Dim lColumn As Long
Dim lTemp As Double
Dim vCategory As Variant

Set WS = ThisWorkbook.Worksheets("MasterData")
lLastRow = FindLastRow(WS, "A")
vCategory = Array("Strongly Agree", "Agree", "Disagree", "Strongly Disagree")
For lColumn = 0 To 3 Step 2
lTemp = Evaluate("=SUMPRODUCT(--($D$2:$D$" & lLastRow & _
"={""" & vCategory(lColumn) & """," & _
"""" & vCategory(lColumn + 1) & """" & "})" & _
")")
lTemp = lTemp / (lLastRow - 1)

WS.Cells(6, lColumn + 12) = lTemp
WS.Cells(6, lColumn + 13) = lTemp
Next lColumn
End Sub

Paul_Hossler
01-13-2016, 08:58 AM
Maybe a pivot table approach would give you more flexibility?

If you're looking for a polished, publish-able report, it'd take more detailed requirements I think esp to handle different options and potential responses


15162