• creating Multiple If Statements depending on specific cell entry

    From andymcgooner@gmail.com@21:1/5 to All on Tue Aug 11 09:22:14 2020
    Help (Again)

    I have spreadsheet that i want to use to track the scores in respects to people predictions for a competition we run each year, however as opposed to updating everyone prediction scores manually i want the sheet to do it. However having trouble working
    out how to the if statements as there are lots of them. Anyway this is what I have.

    Basically i want to compare the predicted score against the actual score and award points i.e.

    6 points for an accurate result
    4 points for a correct result & score difference
    2 Points for a correct result

    Now I have split it into 3 separate bits for now i.e. HW (indicating a Home Win), AW (Indicating an Away Win) & SD (Indicating a Score Draw) and as such can get the formulas to work independently but struggling to combine them all, anyway for each one I
    have the following formulas.

    HW - =IF(F$3="HW",IF(AND(D6=D$3,E6=E$3),6,IF((D6-E6)=(D$3-E$3),4,IF(D6>E6,2,0))))

    AW - =IF(J$3="AW",IF(AND(H6=H$3,I6=I$3),6,IF((I6-H6)=(I$3-H$3),4,IF(I6>H6,2,0))))

    SD - =IF(N$3="SD",IF(AND(L6=L$3,M6=M$3),6,IF((M6-L6)=(M$3-L$3),4,0)))

    However what I want is them all into one column and the cell to pick the right formula dependant on which has been selected for that result.

    Anyway Spreadsheet as yet look likes the following, bearing in mind there might be up to 50 lines of entries all with different predictions


    Result F A F A F A
    2 1 HW 1 2 AW 1 1 SD

    F A Points F A Points F A Points
    Prediction 1 2 1 6 2 1 0 2 1 0
    Prediction 2 3 1 2 1 2 6 1 2 0
    Prediction 3 2 0 2 1 1 0 1 1 6
    Prediction 4 3 2 4 2 3 4 2 2 4
    Prediction 5 3 1 2 3 1 0 3 1 0
    Prediction 6 1 1 0 1 4 2 3 2 0
    Prediction 7 1 2 0 0 2

    Anyway any help in getting it into one stack were the formulas are selected dependant on what type of results it is would be greatly appreciated, if not clear can send a copy of the test spreadsheet i have been working on.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)