Psychologist trying to make custom formula for my evaluations.

[bbelack]

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!!

[daddylonglegs]

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}))

[bbelack]

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?

[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?

[corinereyes]

Hi bbelack,

I tried to make an example of how i understand your scenario. Please see the attached xl file.

In the example:

There are 2 sheets named "Examinee Answer" & "Occupation".

In the "Examinee Answer" sheet, row headings are the questions, q1,q2, so on...green area in the sheet is where you put the examinee's answers.

example: in q1, he answered 3 7 9, in q2 he answered 1,2,3 and in q3 he answered 3 7 9 again...so on...

In the sheet named "Occupation" the formula in cell A3 to K3, counts how many 1's, 2's, 3's appeared in the ""Examinee Answer" sheet.

Please Login or Register  to view this content.

My apologies if this is not what you want.

[bbelack]

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!

[daddylonglegs]

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)

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......

[daddylonglegs]

OK,

changed my mind...here's a stab at a bar chart, created by using this formula in N57 copied across and up

=IF(B$57>ROW(N$57)-ROW(),"x","")

and then using conditional formatting to colour the "x"s, see attached

[bbelack]

OK,

changed my mind...here's a stab at a bar chart, created by using this formula in N57 copied across and up

=IF(B$57>ROW(N$57)-ROW(),"x","")

and then using conditional formatting to colour the "x"s, see attached

DLL,

First, again...thank you for your help.

With regard to the chart, great work, but... the Raw Score Data represented graphically doesn't help us. Percentiles represented graphically are really what we consider when making recommendations.

Also, as you can see by the attached file (.jpg), scores don't represent the same percentile across all vocational categories. So, for example, in Vocation A, a score of 2 is in the 55th percentile. Whereas in Vocation B, a score of 2 is in the 35th percentile.

I wish to then graph those numbers.

Don't worry about the graph though. I hate to take your time.

I really need to have the scores, within each vocational category, automatically represented by a number (the percentile) in the percentile row.

In other words, is there a way to convert the attached jpg chart into a formula for each Vocational column?

[daddylonglegs]

If you just want a formula then try this in B58

=IF(B57="","",CHOOSE(MIN(B57,19)+1,1,3,5,10,10,15,20,25,30,35,40,40,45,50,55,60,70,80,90,99))

Obviously you'll need to adjust for the other columns.

It may be easier to construct a table, though, especially if the relationship between scores and percentiles might change as it's usually easier to change a table than multiple formulas

[bbelack]

If you just want a formula then try this in B58

=IF(B57="","",CHOOSE(MIN(B57,19)+1,1,3,5,10,10,15,20,25,30,35,40,40,45,50,55,60,70,80,90,99))

Obviously you'll need to adjust for the other columns.

It may be easier to construct a table, though, especially if the relationship between scores and percentiles might change as it's usually easier to change a table than multiple formulas

DADDY!

After tooling around with everything I got it all to work functionally. You've gotta try it. You fill in the answers, 1-55. Then, it tallies the scores. Then, it converts the percentiles! THEN! It's represented in the graph I made....

JUST ONE PROBLEM...

For some reason, although it totally functions perfectly, in cell K58 it shows an error before any of the scores are entered. I've scrutinized over it over and over again. Yes, it works, but it just looks bad. Can you see what I can't??? It's saying "#VALUE!"

Again, works perfectly, just looks weird...

You're a genius!

Thanks again!! (File is attached)

P.S. The error I'm speaking of only shows before answers are entered. The graph won't move until after answers are keyed in.

[daddylonglegs]

Glad to hear you've got it working.....nice graph, better than mine

Perhaps change the formula in B57 copied across, firstly so that a blank is shown if there are no scores entered...and when you start entering scores only numbers (not blanks) are matched. E.g. change to:

=IF(COUNT($A2:$A$56),SUMPRODUCT(--(B2:B56=$A2:$A56),--($A2:$A56<>"")),"")

You shouldn't need to have more than one 99 in the CHOOSE formulas, if you have a single 99 and then adjust the figure in the

MIN(K57,23)

part so that the number is the lowest score at which the percentile should be 99. In the case of K58 that would look like this, I think

=IF(K57="","",CHOOSE(MIN(K57,16)+1,2,10,20,35,50, 55,65,70,80,85,85,90,90,95,97,98,99))

[bbelack]

Glad to hear you've got it working.....nice graph, better than mine

Perhaps change the formula in B57 copied across, firstly so that a blank is shown if there are no scores entered...and when you start entering scores only numbers (not blanks) are matched. E.g. change to:

=IF(COUNT($A2:$A$56),SUMPRODUCT(--(B2:B56=$A2:$A56),--($A2:$A56<>"")),"")

You shouldn't need to have more than one 99 in the CHOOSE formulas, if you have a single 99 and then adjust the figure in the

MIN(K57,23)

part so that the number is the lowest score at which the percentile should be 99. In the case of K58 that would look like this, I think

=IF(K57="","",CHOOSE(MIN(K57,16)+1,2,10,20,35,50, 55,65,70,80,85,85,90,90,95,97,98,99))

Thanks for that Daddy!

My question is though... why is that cell showing the error and the others are not??

B

[daddylonglegs]

Well, because you don't have enough 99s in there.

If you have a formula like

=CHOOSE(A1,4,5,6)

this means that if A1 is 1 the formula returns 4, if A1 is 2 the formula returns 5 and if A1 is 3 the formula returns 6, but if A1 is any other value, e.g. 4 you'll get an error value.

The way my suggested formula avoided this was by using MIN, e.g.

=CHOOSE(MIN(A1,3),4,5,6)

with this formula if A1 is any value greater than 3 MIN(A1,3) gives 3 and the formula returns 6.

The formula you have in K58 is

=IF(K57="","",CHOOSE(MIN(K57,23)+1,2,10,20,35,50,55,65,70,80,85,85,90,90,95,97,98,99,99,99,99,99,99,99))

but because MIN(K57,23)+1 can be as high as 24 you need 24 values following and you only have 23......so when K57 is greater than 22 you'll get an error.

To fix that you could add another 99.....or change the 23 to 22...

[bbelack]

Daddy...I've been so busy... But I wanted to say thank you for all of your help!! We really appreciate it here!!

It's working like a charm!!

Thanks again!