Noob query

E

Evans8773

New member
Joined
May 20, 2023
Messages
22
Reaction score
1
Points
3
Excel Version(s)
Treadstone7*
Hello

its been years since i used excel and was hoping someone may be able to sort this formulas for me.

basically its a a sports scores prediction sheet.

so if A1=A2 and B1=B2 then lets say cell C1 gets 3 points added to it. i can do it with 1 variaBLE BUT NOT 2..

=(IF(A1=A2,3,0)) That works but dont know how to add the second variable B1=B2

cheers
 
Thanks will check it out :)
 
that worked

so should this also work

=if(and(C6=C29,D6=D29),(G6=G29,H6=H29),(K6=K29,L6=L29),3,false) ??

so the above would score 3 x3 9pts
 
Last edited:
=SUM(IF(AND(C6=C29,D6=D29),3,0),IF(AND(G6=G29,H6=H29),3,0),IF(AND(K6=K29,L6=L29),3,0))
 
p45cal said:
=SUM(IF(AND(C6=C29,D6=D29),3,0),IF(AND(G6=G29,H6=H29),3,0),IF(AND(K6=K29,L6=L29),3,0))
Click to expand...
slight problem, when i drag the cell down (relative refencing) ? the first part of each is correct it changes with the line but the second part is always wrong c43,d43,g43,h43,k43,l43 should all be 29.....is there a way around thi without manually changing them ??
thanks

=SUM(IF(AND(C20=C43,D20=D43),3,0),IF(AND(G20=G43,H20=H43),3,0),IF(AND(K20=K43,L20=L43),3,0))
 
=SUM(IF(AND(C6=C$29,D6=D$29),3,0),IF(AND(G6=G$29,H6=H$29),3,0),IF(AND(K6=K$29,L6=L$29),3,0))
 
I have 1 last query though i cant get my head around, in the screenshot Alex has predicted 2 scores correctly so scores 3 points per game and the last game he has got 1 teams score correct.

what i need is the formula for my 1 point column that says

j7 scores 1 point for getting part of the score correct, however if the other part is correct (i7) 3 points is awarded not 1for getting part of it correct.

so correct score gets you 3 points, half of it right 1 point

at present i have it as 3 points for correct score and 1 point each for each individual team scores so a correct score is giving you 5 not 3





last query i promise..

cheers
 

Attachments

  • abc.png
    abc.png
    60.6 KB · Views: 8
Last edited:
All in one:
=SUM(C6=C$29,D6=D$29,G6=G$29,H6=H$29,K6=K$29,L6=L$29,AND(C6=C$29,D6=D$29),AND(G6=G$29,H6=H$29),AND(K6=K$29,L6=L$29))

This can be split into two:
1 point per individual score correct:
=SUM(C6=C$29,D6=D$29,G6=G$29,H6=H$29,K6=K$29,L6=L$29)

plus

1 point for every match completely correct:
=SUM(AND(C6=C$29,D6=D$29),AND(G6=G$29,H6=H$29),AND(K6=K$29,L6=L$29))
 
Last edited:
p45cal said:
All in one:
=SUM(C6=C$29,D6=D$29,G6=G$29,H6=H$29,K6=K$29,L6=L$29,AND(C6=C$29,D6=D$29),AND(G6=G$29,H6=H$29),AND(K6=K$29,L6=L$29))

This can be split into two:
1 point per individual score correct:
=SUM(C6=C$29,D6=D$29,G6=G$29,H6=H$29,K6=K$29,L6=L$29)

plus

1 point for every match completely correct:
=SUM(AND(C6=C$29,D6=D$29),AND(G6=G$29,H6=H$29),AND(K6=K$29,L6=L$29))
Click to expand...
thank you, i will add it to the sheet later,,,thanks again
 
Hi all, once again thanks for the help. I have another query about how to get the sheet to auto sort based on points in column Q. I do it manually but would like to get it to sort itself as input the final scores. I believe its VBA ? Hope someone can help... Thanks
 

Attachments

  • Screenshot 2023-09-03 090655.png
    Screenshot 2023-09-03 090655.png
    235.6 KB · Views: 6
Starting by being on another sheet, record a macro of yourself moving to the sheet you want to sort, then doing the sort to your satisfaction.
Post the resultant code here.
(This should really be a fresh thread; it's quite different from your previous topic and will likely involve macro code (vba). However, your version of Excel may allow this to be solved with just formulae or Power Query (built in to Excel now), but (a) I'd need to know what version of Excel you're using and (b) it would be more helpful if you were to attach a workbook rather than a picture of one).
 
Sorry will post with more info in correct forum section
 
You must log in or register to reply here.
Back
Top