Avoiding negative values in IF statements help needed
 

Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil

Avoiding negative values in IF statements help needed
 

Maybe

=IF(D2-E2=7,0,IF(D2-E2=1,1,IF(D2=E2,2,IF(ABS(D2-E2)<=6,3,4))))

Mike


" wrote:

Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil

Avoiding negative values in IF statements help needed
 

Try this:

=IF(COUNT(D2:E2)=2,LOOKUP(E2-D2,{-100,-6,0,1,7},{0,1,2,3,4}),"")



" wrote:

Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil

Avoiding negative values in IF statements help needed
 

Hi,

This problem might be better solved with a different approach:

1. Create the following table

Difference Points
-100 0
-6 1
0 2
1 3
7 4

Suppose this is in A1:B6, including titles. Then your formula is

=VLOOKUP(D2-E2,$F$5:$G$9,2,1)

If you could loose by more than 100 points adjust the first number.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

" wrote:

Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil

Avoiding negative values in IF statements help needed
 

On 22 Nov, 22:23, Shane Devenshire
wrote:
Hi,

This problem might be better solved with a different approach:

1. Create the following table

Difference * * *Points
-100 * *0
-6 * * *1
0 * * * 2
1 * * * 3
7 * * * 4

Suppose this is in A1:B6, including titles. *Then your formula is

=VLOOKUP(D2-E2,$F$5:$G$9,2,1)

If you could loose by more than 100 points adjust the first number.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire



" wrote:
Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil- Hide quoted text -

- Show quoted text -

Thanks all,
I have used Teethless mama's approach and it works well. I did try
them all, but liked that one.

Not sure what you mean about pressing the Yes button Shane. I'm using
Google Groups and don't see anything like that.

Neil

Avoiding negative values in IF statements help needed
 

Ignore it, Neil. The "Yes button" to which Shane refers is a function
merely of Microsoft's web interface to the newsgroup, and is not
visible to people accessing the newsgroup directly via a news server,
or accessing via one of the other web interfaces such as Google
Groups. It would be preferable if those who give the "press the Yes
button" reminders would restrict them to when they are replying to
someone using the Microsoft web interface.
--
David Biddulph

On 23 Nov, 09:52, "
wrote:

Thanks all,
I have used Teethless mama's approach and it works well. I did try
them all, but liked that one.

Not sure what you mean about pressing the Yes button Shane. I'm using
Google Groups and don't see anything like that.

Neil

On 22 Nov, 22:23, Shane Devenshire

wrote:
Hi,

This problem might be better solved with a different approach:

1. Create the following table

Difference * * *Points
-100 * *0
-6 * * *1
0 * * * 2
1 * * * 3
7 * * * 4

Suppose this is in A1:B6, including titles. *Then your formula is

=VLOOKUP(D2-E2,$F$5:$G$9,2,1)

If you could loose by more than 100 points adjust the first number.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

" wrote:
Hi,

I have the following criteria to asses match results

Away loss by 7 points or more = 0 points
Away loss by 6 points or less = 1
Away draw = 2
Away win by 1 to 6 points = 3
Away win by more than 7 = 4

I used the following equation:
=IF(E2="","",IF(D2-E2<=6,1,IF(E2=D2,2,IF(E2-D2=7,4,IF(E2-
D2<7,3,0)))))

Seemed to mostly work, apart from the last bit. I think I have the
last zero in the wrong place.

This formula if the score was 50-40 (D2=50, E2=40) home win, evaluated
E2-D2<7 as -10 and put 3 in the cell

Thanks for any help

Neil