need more than seven if statements

[robt]

I know i can only nest seven if statements in excel. Trouble is i have 12 conditions! any help much appreciated

cheers
rob

 

[MarkVMcCullagh]

Split the nesting over several cells

 

[Aladin Akyurek]

So many Ifs are seldom needed and the problems for which they are invoked are often solvable with lookup formulas. Care to describe the problem you want solve along with sample data and desired results?

 

[robt]

I am a school teacher,

there are twelve subjects which pupils could take and i am given a predicted points score for each subject e.g. 2.3 for biology, 4.5 for maths etc.

This info is in a spreadsheet, i know that a certain points score equates to a certain grade and i want to turn all these points for each pupil into grades on the sheet.

the pupils are in column a with the subjects across the others in row 1

 

[Northerner]

? Wouldn't =SUM(A2:M2) in N2 get the required results?

 

[robt]

no e.g

joe bloggs has these scores for twelve subjects:
each subject needs to be converted to a grade
4.3
5.4
8.7
1.2
3.2
5.6
7.6 etc etc

I know that anything greater than 1.3 is a 'G' grade, greater than 2.3 is 'F/G'. etc Iwould usually use a nested IF but with so many grades there are too many

 

[Airwolf]

Hi

Alan is correct lookup will do the job. If you set para 4 of the lookup to 1 then a failure to find a value will result in the nearest value below is returned. This is ideal for returning grade boudaries:-

Score Grade
1.3 G
2.3 F/G
3.3 F

By looking up the score for instance 2.5 the return would drop back to 2.3 then return F/G as grade, if socre is 2, G would be returned

=Vlookup(B1,Table_1,2,1) where B1 is the value being tested Table_1 is a range name for where your grade boundary table is the 2 is for the 2nd col in the table(to return) and the 1 sets the list as 'organised' - ie your grades/scores MUst ascend down the table.

 

[robt]

hi,

i'm a bit confused. How do i set this up if the pupils are arranged down column a and the subjects are listed across the top?

i.e For all the subjects a pupil will have a score which is different and i want excel to assign a grade to them all. I will copy the subject names and paste them on the end of the sheet so that each pupil will have a list of subjects with the score, and after that the sunjects again but now converted to grades. The pupils will extend to row 200 ish

 

[B_2]

I know i can only nest seven if statements in excel. Trouble is i have 12 conditions! any help much appreciated

cheers
rob

Rob this will do it

=CHOOSE(A1,"5","10","15","20","25","30","35","40","45","50","")

add as many as you want & when you enter 1 in cell A1 it will return 5
when you enter 2 in cell A1 it will return 10 and so on

 

[x-amount]

I would use the lookup method in this case, but could another way of beating this "if" holdback, would be splitting the difference, or have half the values in one side of the if.


This way you can have up to 2^7=128 different choices

so have nested ifs, like this

=if((choice number<=64),if(choice number<=32),if(choice number<=16),

etc.

(and, of course theres the choice function)
:P