formula

[RmB]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Calculations result in the following:
Cells F1:N1 read across.. Win,Tie,Win,Tie,Tie,Win,Win,Win,Tie.
Each cell is worth 1 unit but the value of a tied cell is carried over to the next. Hence the unit value result for the cells is...1,0,2,0,0,3,1,1,0...which will appear in cells F2:N2 but I just can't come up with the formula.

Thanks for any help. RmB

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Calculations result in the following:
Cells F1:N1 read across.. Win,Tie,Win,Tie,Tie,Win,Win,Win,Tie.
Each cell is worth 1 unit but the value of a tied cell is carried over to
the next. Hence the unit value result for the cells
is...1,0,2,0,0,3,1,1,0...which will appear in cells F2:N2 but I just can't
come up with the formula.

Thanks for any help. RmB

Assuming there isn't any other option (e.g., "Lose"), here's one way:

F2: =--(F1="Win")
G2: =IF(G1="Win",COLUMN()-COLUMN($E$1)-SUM($F$2:F2),0)

Copy G2 to the right as far as required.

 

[RmB]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Calculations result in the following:
Cells F1:N1 read across.. Win,Tie,Win,Tie,Tie,Win,Win,Win,Tie.
Each cell is worth 1 unit but the value of a tied cell is carried over to
the next. Hence the unit value result for the cells
is...1,0,2,0,0,3,1,1,0...which will appear in cells F2:N2 but I just can't
come up with the formula.

Thanks for any help. RmB

Assuming there isn't any other option (e.g., "Lose"), here's one way:

F2: =--(F1="Win")
G2: =IF(G1="Win",COLUMN()-COLUMN($E$1)-SUM($F$2:F2),0)

Copy G2 to the right as far as required.

No "Lose" only wins or ties. This works great. Thanks again. RmB[/QUOTE]