Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF IF IF
First two rows look like this, beginning with column headings:
TM ACTION FOR AGAINST Was. 3FGM 62 54 In succeeding rows, when TM = Was. and ACTION = 3FGM or FGM or FTA, I want to add 3, or 2, or 1, respectively, to the numberin the FOR column. If it is anything else but 3FGM or FGM or FTA, I want to add 0 to the number in the FOR column. Furthermore, if TM does not equal Was., I want to perform the same calculations as above, but in the AGAINST column. Make sense? |
#2
|
|||
|
|||
Hi Bob
Firstly, I put two extra columns between ACTION / FOR "C" & "D" In the first one put the below formula =IF(A4="WAS",LOOKUP(B4,{"3FGM";"FGM";"FTA"},{3;2;1 }),0) in the second put your original FOR amount, then in column "E" which should be your original "FOR" column put: =D4+C4 I also added 2 columns "F" & "G" between FOR /AGAINST Using the same principal as above except in "F" put: =IF(A4<"WAS",LOOKUP(B4,{"3FGM";"FGM";"FTA"},{3;2; 1})) and in "G" put. =G4+F4 You can now hide the helper columns if you don't need them. If the amounts in FOR /AGAINST are fixed then the extra columns won't be needed. This is a pretty rough way round the issue and there will no doubt be far better answers,.......but it is Friday and you might want it NOW !! HTH Michael = "bob" wrote: First two rows look like this, beginning with column headings: TM ACTION FOR AGAINST Was. 3FGM 62 54 In succeeding rows, when TM = Was. and ACTION = 3FGM or FGM or FTA, I want to add 3, or 2, or 1, respectively, to the numberin the FOR column. If it is anything else but 3FGM or FGM or FTA, I want to add 0 to the number in the FOR column. Furthermore, if TM does not equal Was., I want to perform the same calculations as above, but in the AGAINST column. Make sense? |
#3
|
|||
|
|||
Hi!
This will work as long as there will be no empty rows in between entries: In the "FOR" column: =IF(OR(A3="",B3=""),"",IF(A3="was.",IF(B3="3fgm",C 2+3,IF (B3="fgm",C2+2,IF(B3="fta",C2+1,C2))),C2)) In the "AGAINST" column: =IF(OR(A3="",B3=""),"",IF(A3<"was.",IF(B3="3fgm", D2+3,IF (B3="fgm",D2+2,IF(B3="fta",D2+1,D2))),D2)) Biff -----Original Message----- First two rows look like this, beginning with column headings: TM ACTION FOR AGAINST Was. 3FGM 62 54 In succeeding rows, when TM = Was. and ACTION = 3FGM or FGM or FTA, I want to add 3, or 2, or 1, respectively, to the numberin the FOR column. If it is anything else but 3FGM or FGM or FTA, I want to add 0 to the number in the FOR column. Furthermore, if TM does not equal Was., I want to perform the same calculations as above, but in the AGAINST column. Make sense? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|