Football (Soccer!) points scoring

[Onceageordie]

Hi,

I have 2 cells, both of which contain 3 characters. These can be W, D, L or -. I want to count how many "real" characters there are (W, D or L) as GAMES. A maximum points score will be GAMES * 3 (MAXSCORE). Where W = 3, D = 1 and L = 0 I need a total across the 2 cells (TOTAL). Finally TOTAL / MAXSCORE will give me a value. How can I get this into a formula?

Many thanx

[daddylonglegs]

Are the cells adjacent? If so then use this for a horizontal range like A2:B2 [edited]

=IFERROR(SUMPRODUCT((LEN(A2:B2)-LEN(SUBSTITUTE(A2:B2,{"W";"D"},"")))*{3;1})/SUMPRODUCT(LEN(SUBSTITUTE(A2:B2,"-","")))/3,0)

[daddylonglegs]

....or this method is shorter and will allow you to use any two cells (adjacent or not) but requires "array entry"

=IFERROR(AVERAGE(IF(MID(A2&B2,{1,2,3,4,5,6},1)={"W";"D";"L"},{3;1;0}/3)),0)

confirmed with CTRL+SHIFT+ENTER

[Onceageordie]

ok, I like the second method. I have a further small complication. I actually have 3 cells with data (Home Form, Away Form & Head 2 Head). For the Home score I use Home Form & Head 2 Head, for the Away score I use Away Form & Head 2 Head. The problem is the Head 2 Head data is based on the Home team (ie W = win for home team). Therefore I need the W and L to be switched for the Away calculation.

Thanks again.

[Onceageordie]

So I used the SUBSTITUTE command to change the W to L in a hidden column and it all now works as expected.

Not elegant, I know, but it does the job.

Many thanks again.

[daddylonglegs]

To switch the points you can just adjust the {3;1;0} part which are the points assigned respectively to {"W";"D";"L"} so you can make that {0;1;3} for the away calculation, i.e.

=IFERROR(AVERAGE(IF(MID(A2&B2,{1,2,3,4,5,6},1)={"W";"D";"L"},{0;1;3}/3)),0)