求助：修改Excel IF计分公式，仅当E1=F1且G1=H1时生效

Got it, let's fix up your formula to include the team matching prerequisite you need. Here's how to structure it properly:

Final Modified Formula #

=IF(AND(E1=F1,G1=H1), IF(OR(A1="",B1=""),"", IF(AND(A1=C1,B1=D1),5, IF(OR(C1-D1=A1-B1,AND(C1>D1,A1>B1),AND(D1>C1,B1>A1)),2,0))), "")

Breakdown of the Logic #

Let's walk through each layer so you understand exactly what's happening:

1. Prerequisite Check: The outer IF(AND(E1=F1,G1=H1), ..., "") first verifies if your team pairs match (E1=F1 and G1=H1). If this condition fails, it returns an empty string ("") and skips all scoring logic.
2. Empty Score Handling: If the team check passes, it next checks if either actual score cell (A1 or B1) is empty. If so, it returns an empty string to avoid calculating scores for incomplete data.
3. Full Correct Score: If actual scores are present, it checks if the prediction (C1/D1) exactly matches the actual result (A1/B1). A perfect match gets 5 points.
4. Correct Match Outcome: If the full score isn't a match, it checks three valid "correct outcome" scenarios:
   • Prediction goal difference matches actual goal difference (C1-D1=A1-B1)
   • Both prediction and actual result have the home team winning (C1>D1 and A1>B1)
   • Both prediction and actual result have the away team winning (D1>C1 and B1>A1)
     Any of these scenarios earn 2 points.
5. No Points: If none of the above conditions are met, it assigns 0 points.

Quick Tweak Option #

If you'd prefer to return 0 instead of an empty string when the team prerequisite isn't met, just replace the final "" in the formula with 0.

内容的提问来源于stack exchange，提问作者DanfrancoZola