Formula Errors

Excel Formula to Score Correct Football Predictions

Olivia Miller Oct 10, 2026 872 views

Question details

The user needs an Excel formula to award 3 points for correctly predicting a football match score and another 3 points for correctly predicting the goal scorer.

How to Create an Excel Formula to Score Correct Football Predictions

Product: Excel
Device & OS: not provided
Scenario: Creating a football prediction game spreadsheet where player predictions must be compared to actual results to calculate scores.
Observed behavior: The user requires the correct logical formula to compare the prediction columns with the actual columns and needs help applying it across all rows without causing array errors.

Before you start

Ensure that your predicted scores and actual scores are entered in separate, clearly labeled columns so the formula can accurately compare the two sets of data.

Solution 1Recommended

Use the IF Function to Calculate Prediction Points

Use the IF formula to compare predicted results with actual match data and award points automatically.

The IF function is the most effective way to compare two cells and return a specific value if they match. In this scenario, we will use separate IF functions to score the match prediction and the goal scorer prediction.

Select the scoring cell

Click on the cell where you want the match score points to appear (for example, P4).

Enter the match score formula

Type =IF(C4=E4,3,0) and press Enter. This formula checks if the predicted score in C4 matches the actual score in E4. If they match, it awards 3 points; otherwise, it awards 0.

Enter the goal scorer formula

Click on the cell for the goal scorer points (for example, Q4) and type =IF(F4=D4,3,0). This compares the predicted scorer in F4 with the actual scorer in D4.

Drag to fill the remaining rows

Select both formula cells (P4 and Q4). Hover over the lower-right corner of the selection until the cursor turns into a solid crosshair, then click and drag down to apply the formulas to the rest of your table.

Use the IF Function to Calculate Prediction Points

Avoid Array Errors: Do not manually edit the formula to include range arrays like C4:C49=E4:E49. Simply dragging the formula down from the first row will automatically adjust the cell references correctly.

Simplify Data Management

Easily Manage Betting and Prediction Sheets with WPS Office

WPS Spreadsheet provides robust formula support, making it simple to track scores, compare data, and manage complex IF logic for your football predictions.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your football predictions.
2. Apply the IF formula: Type your =IF() function into the desired scoring column to compare predicted values with actual results.
3. Auto-fill the data: Use the fill handle at the bottom-right corner of the cell and drag it down to apply the calculation to all participants effortlessly.

Fully compatible with Microsoft Excel formulas and functionsIntuitive drag-and-fill handles for quick calculationsLightweight application that runs smoothly on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does dragging the formula create an error instead of calculating?

If you manually type a range into the formula (such as C4:C49=E4:E49) instead of dragging the fill handle, the spreadsheet might misinterpret it as an array formula. Always write the formula for the first row (e.g., C4=E4) and use the small square at the bottom right of the cell to drag standard references down.

Can I award different points for a partially correct prediction?

Yes, you can use a nested IF formula or the IFS function to award different point tiers. For example, =IF(C4=E4, 3, IF(LEFT(C4,1)=LEFT(E4,1), 1, 0)) could be used for more complex scenarios where partial points are given for predicting the correct match outcome without having the exact score.

How do I calculate the total score for a participant?

Use the SUM function to add up all the individual points awarded by your IF formulas. For instance, entering =SUM(P4, Q4) in a new 'Total Score' column will give you the combined points for both the match score and goal scorer predictions for that specific row.

Excel & Data Problems