Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statements- HELP needed. Please :) | Excel Discussion (Misc queries) | |||
Excel 2007, show negative in my financial statements in brackets? | Excel Worksheet Functions | |||
avoiding plotting zero values on graphs | Charts and Charting in Excel | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
... Count, <<< Positive Values minus Negative Values >>> ... | Excel Worksheet Functions |