Survey Results Formulas (Excel 97)

Topic

New Reply

I need some help writing a couple of formulas. I am creating a spreadsheet of survey results from a seminar.

1. One question asked users to either check ‘satisfactory’ or fill in a comment. I want to create a formula which says out of X number of surveys sent out Y number answered ‘satisfactory’. I am not sure the exact number or surveys sent out so I want the formula to total the number of surveys sent and tell me out of the total, how many people answered S for satisfactory. The result would look something like 10/50. I am open to other suggestion if you have any.

2. One question asked who have you had contact with A (Assistant), M (Manager) or O (Other). I want a result which totals the number of A, M & Os all in one cell but divided up something like A = 3, M = 8, O = 1.

3. One question asked users to rank 3 considerations when selecting a company. There were 9 options. I want to know what the top 3 overall were.

Any help would be greatly appreciated!!!

Reply | Quote

Replies

Thanks, I am off to a good start.

1. The first part of the formula worked. Is there a way to count the number of cells in a range. Some of these cells are blank. I need to know the total number of cells between D14 and AS14.

2. Some of the cells have two results for example A M. In this case, the formula did not count the A or M. Is there a way to enter the text or write the formula to count these examples?

3. See attached for an example

Reply | Quote

I made a typo, you probably want to use CountA, not count (they are text)
=counta(D14:AS14)
will give a count of the non-empty cells

=Columns(D14:AS14)
will give the count of the cells whether they have anything or not

=count(D14:As14)
will give a count of cells with numbers

2)use wildcards
=COUNTIF(D14:As14,”*A*”)
=COUNTIF(D14:As14,”*M*”)
etc

3) You could get the 9 counts:

=COUNTIF($B$2:$B$100,”*A*”)
=COUNTIF($B$2:$B$100,”*B*”)
…
=COUNTIF($B$2:$B$100,”*I*”)

Then use large(rng,1), large(rng,2) or large(rng,3) to get the top 3 values, where rng is the range of cells containing the 9 counts.

If you had an attachment with the data filled out I could be more specific…

Steve

Reply | Quote

(Edited by sdckapr on 25-May-05 13:39. Changed COUNT to COUNTA, as the cells contain text)

1) Could be done with a Countif divided by a count.(Change range as appropriate)
=Countif(A1:A100,”S”) & ” / ” & CountA(A1:A100)

2) combine 3 countif
=”A = ” & Countif(A1:A100,”A”) &”, M = ” &Countif(A1:A100,”M”)&”, O = ” &Countif(A1:A100,”O”)

3) I would have to see the setup to understand better.

Perhaps you could post a sample file with some made up results…

Steve

Reply | Quote

Thanks. I’ve managed to make the whole thing work. I really appreciate your help. I am getting a much better grasp of formulas!

Reply | Quote

I am glad I could help…

Steve

Reply | Quote