Psychologist trying to make custom formula for my evaluations.

Ok. I'm an assistant to a Psychologist and he has me score these vocational placement tests by hand. It takes a while, and I know through excel we could be cutting down the time dramatically. I'll explain the best way I can.

The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).

There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.

Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.

I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.

I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.

So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.

Hope that makes sense!!

I'm not sure I understand entirely but if you want to count all 3s, 7s and 9s in the range try

=SUM(COUNTIF(L1:L55,{3,7,9}))

7s,9s, and 3s (for ex.), don't always equal the same thing. That keeps the test taker from knowing what they are being tested on (eliminating bias). I want it to be more like, If they answered 9 in question 1, count that in cell A. If they answered 4 in question 2, count that in cell A. It's needs to be more specific in the formula aspect. Isn't there a way I can add multiple conditions to each cell that the answers are being accounted for?

Does that clear it up a little?

Please tell me if I'm way off here but let me see if I understand.

Each question has 3 possible answers. If you choose 1 for question 1 then you have a tendency to vocation K (and possibly one or more other vocations?) if you choose 2 you have a tendency for vocations E and G etc.

Given the 55 answers you want to establish a score for each vocation.

If that's right then perhaps you need to have a table showing, for each question, what the vocational tendencies are for each possible answer. If you create that then you could use a formula at the bottom (or top) to calculate the score for each vocation.

I attach an example with just 10 questions.

The yellow area is where you enter which Vocation corresponds to each possible answer. Blue area is for entering a set of answers for one person and green area shows a "score" for each vocation.

Is this anywhere near what you want?

Originally Posted by daddylonglegs

Please tell me if I'm way off here but let me see if I understand.

Each question has 3 possible answers. If you choose 1 for question 1 then you have a tendency to vocation K (and possibly one or more other vocations?) if you choose 2 you have a tendency for vocations E and G etc.

Given the 55 answers you want to establish a score for each vocation.

If that's right then perhaps you need to have a table showing, for each question, what the vocational tendencies are for each possible answer. If you create that then you could use a formula at the bottom (or top) to calculate the score for each vocation.

I attach an example with just 10 questions.

The yellow area is where you enter which Vocation corresponds to each possible answer. Blue area is for entering a set of answers for one person and green area shows a "score" for each vocation.

Is this anywhere near what you want?

Daddylonglegs! You have been such an amazing help. I had to tool around with it for a while to get it exactly where I wanted it to be.

We're almost done

I need two more steps, but they should be easy at this point considering what you've done so far.

You helped me tally up the answers quickly and efficiently.

What we do now is then look at those totals and each total reflects a percentile.

For example, if in Vocation A the total is 10 then that falls into the 40th percentile. So...

Question 1: Is there a formula I can use that will use a numerical range which I specify, to automatically put in the percentile in it's respective cell? For example, 1=3rd percentile, 2=5th percentile, etc. It's not a set mathematical equation, it's more of a number set. I just need to plug in which values equal what.
Question 2: Then, I want at a glance on the same sheet, for it to be represented graphically (I.e. a bar graph, scatterplot).

I attached the updated version of our sheet, and I think it will make a little more sense when you look at it.

The end result would be:
A. Putting in the patients answers, then [automatically] the tallies going into the score row (which we made already)
B. The score's reflective percentile automatically reflected in its percentile cell, and
C. Ultimately, having it represented graphically right below it.

You're a genius!

Originally Posted by bbelack

Question 1: Is there a formula I can use that will use a numerical range which I specify, to automatically put in the percentile in it's respective cell? For example, 1=3rd percentile, 2=5th percentile, etc. It's not a set mathematical equation, it's more of a number set. I just need to plug in which values equal what.

You might be able to use CHOOSE function, although that will only go up to 29 options, e.g. in B58 copied across

=CHOOSE(B57,3rd,5th,8th,11th,13th)

Extend as far as necessary

This will give you "3rd" if B57 is 1, "5th" if B57 is 2 etc.
To accommodate more than 29 options you could use 2 CHOOSE functions, e.g.

=IF(B57<30,CHOOSE(B57,3rd,5th,8th,11th,13th,.....),CHOOSE(B57-29,18th,23rd,66th,etc,.....))

where 30 is 18th Percentile, 31 is 23rd Percentile, 32 is 66th etc.

....alternatively just use a table somewhere, e.g. in Y1:Y50 list numbers 1 to 50 and in Z1:Z50 list the corresponding percentiles then use this formula in B58

=LOOKUP(B57,$Y1:$Z50)

Originally Posted by bbelack

Question 2: Then, I want at a glance on the same sheet, for it to be represented graphically (I.e. a bar graph, scatterplot).

Graphs and charts aren't really my thing, perhaps somebody else could help you with that......