Formular for multiple conditions

[issa.o]

Hi
friends
i need to have a formular which cut across COLUMNS
for instance COLUMN A to C
column A if marked F
column B if marked 7 to 17 or 18 to 21
the result in column C to be DIV III
and AT THE SAME TIME in
column A if marked A or B or C or D,
column B if marked 7to 17
the result in column c to be DIV I
column B if marked 18to 21
the result in column c to be DIV II
column B if marked 22 to 25
the result in column c to be DIV III
column B if marked 26 to 33
the result in column c to be DIV IV
column B if marked 34 to 35
the result in column c to be DIV O

[etaf]

Nested IF

=IF( AND( A2-"F", B2>=7, B2<=21) , "DIV III", IF( AND( OR(A2="A", A2="B", A2="C", A2="D"), B2>=7 B2<=17) , "DIV 1" , IF(......etc

we can simplify with some array formulas for A -A,B,C or D

but ,

column B if marked 7 to 17 or 18 to 21

does that mean the same as >=7 and <=21
or can you have 17.5 and do not want to include fractions between 17 and 18 ?

[issa.o]

I do not include fractions between 17 and 18

[etaf]

so

column B if marked 7 to 17 or 18 to 21

actually means
B if marked 7 to 21

[jason.b75]

Give this a go

=IFERROR(IF(AND(A2="F",B2=MEDIAN(B2,7,21)),"DIV III",IF(ISNUMBER(FIND(A2,"ABCD")),"DIV "&LOOKUP(B2,{7,18,22,26,34},{"I","II","III","IV","O"}),"")),"")

[JohnTopley]

Try

Table below set up in H2:I6 in my formula

Please Login or Register  to view this content.

=IFERROR(IF(AND($A2="F",$B2<=21),"DIV III",VLOOKUP($B2,H2:I6,2,1)),"")

Assumptions made:

A cannot be "E" i.e. only A, B, C, D, F

No value in B can be less than 7 ( no testing for lower value) : if not true, what result do you want?

[etaf]

=IF(AND(A2="F",B2>=7,B2<=21),"DIV III",IF(AND(OR(A2={"A","B","C","D"}),B2>=7,B2<=17),"DIV 1",IF(AND(OR(A2={"A","B","C","D"}),B2>=18,B2<=21),"DIV II",IF(AND(OR(A2={"A","B","C","D"}),B2>=22,B2<=25),"DIV III",IF(AND(OR(A2={"A","B","C","D"}),B2>=26,B2<=33),"DIV IV",IF(AND(OR(A2={"A","B","C","D"}),B2>=34,B2<=35),"DIV O","not in range"))))))

[issa.o]

All formular does not work i need more help

[issa.o]

All formular gives div one but not what i want

[jason.b75]

Which formula did you try?

Mine works fine for me so if you have problems with it then there may be some inconsistencies in your source data.

A sample file would help us to see the problem.

[JohnTopley]

... error in mine that it did not have absolute references

=IFERROR(IF(AND($A2="F",$B2<=21),"DIV III",VLOOKUP($B2,$H$2:$I$6,2,1)),"")


However worked OK with my sample data .

[Teethless mama]

Which formula did you try?

Mine works fine for me so if you have problems with it then there may be some inconsistencies in your source data.

A sample file would help us to see the problem.

If A2=(A,B,C, or D) and B2 is greater than 35. Your formula returns DIV O ---- FAIL

[issa.o]

Yes of course but i want to have a penalt to get div III for those who score F in column A2 although they score bellow 21 WHERE THEY CAN SCORE DIV I OR II IF IN COLUMN A2 THEY GET A,B,C,D but for those who score F in A2 and in B2 score points from 22 to 35 they are not required to get penalt but to have they are origin divi which are III ,IV, 0

[jason.b75]

If A2=(A,B,C, or D) and B2 is greater than 35. Your formula returns DIV O ---- FAIL

Read post #1, the information provided indicates that 35 is the upper limit for values in column B, so anything greater would be an inconsistency in the source data.

The only FAIL is in your ability to process the information in the thread correctly.

[issa.o]

Soo you mean the above formular it works?

[Tony Valko]

The only FAIL is in your ability to process the information in the thread correctly.

Teethless mama is a well known troll on this site.

That's what they do. I suggested that they be banned a long time ago.

[jason.b75]

All of them work with test data.

There is little more we can do without a sample file from you to establish the differences.

[issa.o]

If you test the above formular and it works well
sorry may you attach a file my be i was not able to write formular correct

[JohnTopley]

See attached with the 3 formulae.