Help with a (simple?) formula for a marking system?

[Casablanca23]

Hiya

I'm not great with excel, but I thought this would be pretty simple (apparently not for me). I have a marking system whereby column A is the question number, B, C and D are criteria, and E is a final score. Essentially, each question can have a certain number of issues wrong in each criteria (maximum of 3 per criteria), and dependant on how many is wrong it will add a score to D. I'm writing my formula so that if I put a '1', '2' or '3' in the criteria columns, it will allocate a certain point (ie. 2 things wrong will get a higher point). Its possible to have numbers in just one or all three criteria columns.

The most simplest of questions can only get a maximum of 1 thing wrong per criteria, and so my formula looks like this:

=IF(B12=1,2)+(IF(C12=1,1)+(IF(D12=1,1)))

This formula works fine, and allocates the points into E depending on what I type in, so a maximum of 4 points can be reached. As an example question, this would be 'What is your name?'. You can only get your name wrong once, but in three different ways (forgetting to write it completely, writing down someone elses name, or spelling your name wrong).

However, some of the questions are more complex and have multiple answers, and so I'm trying to put in that, if for example, one criteria has 2 things wrong on it it will get more points than if it only has 1. I've typed my formula like this:

=IF(B11=1,10,IF(B11=2,11,IF(B11=3,12)+(IF(C11=1,10,IF(C11=2,11,IF(C11=3,12)+(IF(D11=1,3)))))))

This would be for a question like 'How many address have you lived at?'. If you've lived at 3 addresses, you can get this question wrong 3 times and in 3 different ways. So for example, if you forget to write down one address you lose 10 points, 2 addresses you lose 11 and so on. If you write down three addresses but none of them are your own, you lose 12 points - and if you spell any of them wrong you'll get 3 points on top of that.

If I type in a single criteria column it works fine, but if I type in more than one it won't add up the totals and it appears that column B seems to override anything else (so if I put a 2 in C, the score will be 11, but if I then also add a 1 to B, it will change the score to 10 instead of making it 21).

I don't know if any of this makes sense, I'm awful at explaining myself.. but what have I done wrong?

 

[marious]

can you post some sample data?
Jeanie is an add-in for excel that generates html out of your excel spreadsheet and then you just copy and paste into the forum
This might help Download

 

[Casablanca23]

Unfortunately not - we have separate computers for work and internet so I don't have excel on this computer (annoying I know!)

 

[jardenp]

I think this will get really hairy, really quick if you use nested IF formula.

How about adding three "criteria base point" columns (which you could then hide when using the workbook)

Something like this (using the info from your row 11 example):

Row	A	B	C	D	E	F	G	H
1	Question	Criteria 1 Base	Criteria 2 Base	Criteria 3 Base	Criteria1	Criteria2	Criteria3	Total Score
11	10(presumably)	9	9	2	1	2	1	=IF(E11>0,B11+E11,0)+IF(F11>0,C11+F11,0)+IF(G11>0,D11+G11,0)

<tbody>
</tbody>

The above H11 cell should yield 24

If all cells in H:H had that formula, you could change the Criteria Bases and Criteria Values as needed and hide columns B:D when using the workbook.

Also, it's interesting to guess what real-world application people's examples/questions come from. I'm having a hard time with yours!

 

[Joyner]

Hello, it looks like your syntax is wrong - you appear to be putting some closing parenthesis in the wrong places.

Try:

=IF(B11=1,10,IF(B11=2,11,IF(B11=3,12,"")))+IF(C11=1,10,IF(C11=2,11,IF(C11=3,12,"")))+IF(D11=1,3,"")

Plus, I added a value if FALSE to the last IF ("") so you won't get FALSE returned in some cases.

Another option for something like this would be to use the CHOOSE function

 

[Casablanca23]

Oh my god that's worked perfectly!!!! Thank you. I'm not excel minded so I can't look at a formula and see what's wrong (they're just mumble jumble to me anyway), I literally just trial and error everything. Thank you so much!

 

[Joyner]

You are welcome