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.(Note: I have used the expression "" to leave blank spaces)Only one result will be returned leaving the other two cells blank.

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.

## Bookmarks