IF statement which will result in one of three values

Hi There,

Question:
How would I write the formula of an IF statement which will result in one of three values, either "H", "D" or "A"?

Scenario:
Suppose I have a number value in cell J3 (it can be either a positive or negative number), and a number value in cell P3 (equally a positive or negative number). Cells J3 (Home) and P3 (Away) represent the 'form factors' of teams about to compete with each other. The values in each cell have been arrived at by other calculable operations based on factual data. I want to determine which team has the 'greater' form factor and thus (in theory) most likely to win? At the same time, if both team's form factors fall within a certain range of each other, then this will determine (again in theory) a draw.

Cells L3, M3, and N3 is where the final result will appear - either a "H" in cell L3 (representing a 'Home Win'), or a "D" in M3 (representing a 'Draw'), or an "A" in N3 (representing an 'Away Win'), determined by a subtraction between cells J3 and P3. Only one result will be returned leaving the other two cells blank. (Note: I have used the expression "" to leave blank spaces)

The expressions to return either a "H" or a "D" or an "A" are...

Cell L3 =IF(J3-P3>6,"H","")

Cell M3 =IF(J2-P3<=5,"D","")

Cell N3 =IF(J3-P3>6,"A","")

Example A: Cell J3 = -2, and Cell P3 = 5 (should return an 'A', but currently returning both a 'D' and an 'A')

Example B: Cell J3 = -2, and Cell P3 = -5 (should return a 'D' and does, seems ok)

Example C: Cell J3 = 14, and Cell P3 = -14 (should return a 'H', but currently returning both a 'D' and a 'H')

What's To Be Satisfied?

1) The values showing in cells J3 and P3 will fall within a range between -20 to 20, from a negative threshold to a positive threshold, and thus excel needs to recognize that a 'positive' number is greater than a 'negative' number so that it calculates which result to apply and into which cell to apply it to correctly.

2) If cells J3 and P3 fall within a value threshold of 5 to each other, that is to say, the difference between them is only 5 points then the expression should return a 'D'.

3) Both Cells L3 and N3 can return their results at the same time, because the IF statements are the same. Overcoming this will be determined by 1)? I only want one to return a result.

4) It would be great if I can combine the expressions to operate in only one cell, can this be achieved?

I'm almost there, just need a little help in making the statement more succinct.

Many thanks for reading.

maybe something like this

Formula:

Please Login or Register  to view this content.

Hi VLady,

Many thanks for your response. I tried your IF statement but it currently results in wrong returns.

=IF(AND(J3-P3>0,J3-P3<6),"D",IF(J3-P3>6,"H",IF(J3-P3<0,"A","")))

Here are some values and their expected results with the results your statement achieves...

Cell : Value : Cell : Value : Expected Result : VLady Result
J3 : -2 : P3 : -5 : 'D' : 'A' wrong
J4 : 9 : P4 : -3 : 'H' : 'H' right
J5 : -7 : P5 : 5 : 'A' : 'A' right
J6 : -14 : P6 : 15 : 'A' : 'A' right
J7 : -6 : P7 : -12 : 'H' : 'A' wrong
J8 : -3 : P8 : -7 : 'D' : 'A' wrong
J9 : 11 : P9 : 11 : 'D' : 'H' wrong
J10 : -7 : p10 : -4 : 'D' : 'A' wrong

...any chance the expression could be tweaked in some way so that it delivers the expected results? Love the fact I only need to use one cell instead of three.

Once again, many thanks.

=if(and(j3-p3>-6,j3-p3<=5),"d",if(j3-p3>=6,"h",if(j3-p3<0,"a","")))

it's coming from my end good, here's a file with your sample numbers and the formula.

copy book1.xlsx