IF IF IF

[bob]

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?

 

[Michael]

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

=

 

[Biff]

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",C2+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