Correct Formula Syntax Required...

Hi There,

I'm hoping that a more knowledgeable member can supply me with the solution to a formula syntax problem?

The Scenario:

Four cells are related to each other by football scores. Thus; cell D2 is the home team cell in which the amount of goals the home team actually score is entered. Cell E2 is the away team cell in which the amount of goals the away team actually score is entered.

In cells F2 and G2 is entered a predicted score prior to the match, F2 = home team, G2 =away team. If the predicted score matches the actual result exactly, then in cell H2 a '3' is given, if the prediction only gets the home or away result right, but not the exact score line, then in cell H2 a '1' is given instead.

What I am Looking to Achieve

I want everything automated apart from entering the actual score of the match, which will be entered manually into cells D2 and E2. Cell H2 will remain blank until the score line is manually entered in D2 and E2. If the predicted score in cells F2 (home team) and G2 (away team) exactly match the score manually entered into cells D2 and E2, then a 3 (as points) will automatically appear in cell H2. If the predicted score does not exactly match the actual score, but gives the right result for either a home or an away win or a draw, then a 1 (as points) will automatically appear in cell H2. If the predicted score does not match the score line, nor give the right result, then a 0 (as no points) will automatically appear in cell H2.

The Problem

I currently have the first half of the formula (entered into cell H2) almost up and running - =IF(AND(D2=F2,E2=G2),3,0) -, but this only gives me a 3 for exactly matching the score, and a 0 for everything else. Suppose the match was predicted a 2 -1 home win, but the actual score was 1 - 0? This should make 1 appear automatically in cell E2, but I can't get it to do this.

Can anyone help me out?

Kind regards to all

Rather than a series of nested (IF(AND(),...), I see a sequence of additions:
1) If home actual=home predicted, then add 1
2) If away actual=away predicted, then add 1
3) If total score actual=total score predicted, then add 1

I would probably use something like =IF(D2=F2,1,0)+IF(E2=G2,1,0)+IF(SUM(D2:E2)=SUM(F2:G2),1,0). That should give you 0 if neither matches, 1 if one or the other matches, and 3 if they both match. The whole thing can be nested inside of another IF() function to test for blank/not entered and do nothing if they are blank.

Hi all. Perhaps:

Please Login or Register  to view this content.

Or even:

Please Login or Register  to view this content.

Hi Guys,

We're not quite there yet. Suppose the following table...

Predictions.jpg

With the result not yet entered under 'RESULT', there should be no score for prediction, each one should either be blank or a zero. For tidiness, blank is preferred, but zero will do as well. The table will consist of 10 matches, with each player seeking to predict the exact score for 3 points, or at the least the right result as a 'Home win', or an 'Away win', or a 'Draw' for 1 point. The table, here shown, is that of the first match, there will be 9 more matches following underneath.

The formula will be placed in the cells that are going to display the score for each of the 7 players (in darker blue). I have tried each suggested formula, and the one that currently works best, with the cell fields changed in the formula for each player, is the 2nd formula given by Leelnich. However, because the beginning of Leelnich's formula begins with a range "D2:G2", scores of predictions from the other player predictions do not appear as either blank or as a zero. Each player prediction has to be treated independently, so a range at the beginning of the formula will not work as intended.

Are we able to adapt the formula so that it does not include the other player predictions? For instance, if the result of this particular match was 1 - 0, the score for each player would be as follows: Barry = 3, Chris = 1, David = 0, Glenn = 0, Mick - 1, Ryan = 0, and Sam = 0. Of course, the formula should work for the other 9 matches, too. If no result entered, then all score predictions should either be blank or zero.

Best regards

Aristillus

I'm not sure I understand the new wrinkle in the problem. Pictures are so hard to debug from because they usually don't allow us to see the formula (and other aspects) that is actually in each cell.

However, because the beginning of Leelnich's formula begins with a range "D2:G2", scores of predictions from the other player predictions do not appear as either blank or as a zero. Each player prediction has to be treated independently, so a range at the beginning of the formula will not work as intended.

I'm not sure I understand what you mean here. If you want the formula to refer to the results entered into B2:C2, then the formula must contain a reference of some kind to that range.

Reading between the lines and guessing, is it possible you are missing the basic spreadsheet programming concept of relative and absolute referencing? If leelnich's formula works correctly in F3, then it seems like it should have easily copied correctly into H3, J3, L3, N3,... with the correct combination of relative and absolute references (specifically fixing the B and C column references so they are fixed column references). If you are careful to fix only the column reference and not the row reference in these references, then it should also copy down to row 5, row 7, row9, etc. correctly. Overview of formulas, including a section discussion relative and absolute referencing: https://support.office.com/en-us/art...rs=en-US&ad=US

Did I guess correctly, or is there something I am misunderstanding about this latest part of your question?

Hi There,

Predictions 3.jpg

What this image shows is that the actual result of the match has not been entered, therefore, all scores for predictions should be blank, but as you can see, from Chris to Sam there are scores showing. The formula does work to show the correct scoring of the predictions when the actual result is entered, which in the case of this match was 0 - 3. It could be that I am not using the correct cell references for each player, but as it stands these are the formulas for each player...

Barry: =IF(COUNT(D2:G2)<4,"",IF(AND(D2=F2,E2=G2),3,IF(SIGN(D2-E2)=SIGN(F2-G2),1,0))) works correctly

Chris: =IF(COUNT(D2:J2)<4,"",IF(AND(D2=I2,E2=J2),3,IF(SIGN(D2-E2)=SIGN(I2-J2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

David: =IF(COUNT(D2:M2)<4,"",IF(AND(D2=L2,E2=M2),3,IF(SIGN(D2-E2)=SIGN(L2-M2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

Glenn: =IF(COUNT(D2:P2)<4,"",IF(AND(D2=O2,E2=P2),3,IF(SIGN(D2-E2)=SIGN(O2-P2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

Mick: =IF(COUNT(D2:S2)<4,"",IF(AND(D2=R2,E2=S2),3,IF(SIGN(D2-E2)=SIGN(R2-S2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

Ryan: =IF(COUNT(D2:V2)<4,"",IF(AND(D2=U2,E2=V2),3,IF(SIGN(D2-E2)=SIGN(U2-V2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

Sam: =IF(COUNT(D2:Y2)<4,"",IF(AND(D2=X2,E2=Y2),3,IF(SIGN(D2-E2)=SIGN(X2-Y2),1,0))) works correctly but shows a score when the actual score has not been entered. It should be blank like Barry.

Hope I'v been able to clarify what I am after achieving?

Best regards

Aristillus

Untitled.png

Based on your layout from post #8, paste this in H2 and copy down:

Please Login or Register  to view this content.

Select H2:H11 and press CTRL+C to copy. Then, while holding down the CTRL key, select K2,N2,Q2,T2,W2, and Z2. Now right-click and select Paste Formulas.

NOTE: The "$" dollar signs are critical, as they determine which address components are ABSOLUTE (unchanging), as opposed to RELATIVE (varying in each instance of the formula based on the position of the containing cell).

Happy to help, thanks for the rep! If that concludes your thread, please got to Thread Tools (up top) and mark it as SOLVED. Regards - Lee