Calculation Issues

How to Calculate an Excel Average While Including a Fixed Cost

Steven ParkerSteven Parker Nov 13, 2026 869 views

Question details

The user needs to calculate Month 4 values in Excel by averaging earlier monthly cost values while retaining a fixed cost that is already included in the Month 4 total.

How to Calculate an Excel Average While Including a Fixed Cost
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user is preparing a Month 4 calculation that averages earlier monthly cost values for two rows and must keep a fixed cost already included in the Month 4 total.
Observed behavior
The Month 4 total contains a fixed cost, so a simple average over earlier months would ignore that fixed cost relationship or misstate the calculation.
Before you start

Confirm that B2:D2 and B3:D3 contain the three earlier monthly costs, E4 contains the fixed cost, and E5 contains the Month 4 total. Adjust these references if your worksheet uses a different layout.

Solution 1Recommended

Use an AVERAGE formula that keeps the fixed cost in the Month 4 total

Use this solution when the Month 4 total already includes a fixed cost and you need to allocate only the remaining variable amount based on earlier monthly averages.

This formula divides the row-specific monthly average by the combined average of both rows, then multiplies by the Month 4 total minus the fixed cost cell. The fixed cost remains in the total but is not added again to the averaged base.

1
Select the first Month 4 result cell

Click cell E2, or the first cell where you want the calculated Month 4 value to appear.

2
Enter the AVERAGE formula

Type =AVERAGE(B2:D2)/(AVERAGE(B$2:D$2)+AVERAGE(B$3:D$3))*(E$5-E$4) and press Enter.

3
Fill the formula down

Use the fill handle at the lower-right corner of E2 and drag it down to E3 so row 3 uses the same calculation with its own row average.

4
Verify the references

Check that B2:D2, B3:D3, E4, and E5 match your actual Month 1–3 cost cells, fixed cost cell, and Month 4 total cell; adjust references if the worksheet layout differs.

Use an AVERAGE formula that keeps the fixed cost in the Month 4 total
Formula returned the correct Month 4 values: The formula now averages earlier costs and keeps the fixed cost included in E5 without double-counting it.

Calculate Month 4 Averages in WPS Office Spreadsheets

Use WPS Office Spreadsheets to enter this Excel-compatible AVERAGE formula, fill it across rows, and keep your fixed cost allocations accurate without extra software.

  1. 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the Month 1–3 cost data, fixed cost, and Month 4 total.
  2. 2. Locate the formula cells: Click the cell where the first Month 4 result belongs, such as E2, and select the formula bar.
  3. 3. Enter and fill the formula: Type the AVERAGE formula, press Enter, then drag the fill handle down to the next row.
  4. 4. Review and save: Confirm the results and save the file in .xlsx format or WPS format as needed.
Enter Excel-compatible AVERAGE formulas directly in WPS OfficeOpens and saves .xlsx Excel files without conversion issuesFree, lightweight spreadsheet app with a familiar ribbon interfaceMixed cell references and fill-down work the same as in Excel
microsoft office alternative - wps office

Frequently Asked Questions

Can I use this formula if my earlier months are in columns A, B, and C instead of B, C, and D?

Yes. Replace B2:D2 and B3:D3 with the actual ranges for your Month 1–3 values, and update E4 and E5 to your fixed cost and Month 4 total cells.

Why does the formula subtract E4 from E5?

E4 is the fixed cost that is already included in the Month 4 total. Subtracting it from E5 leaves the variable portion to be allocated by the average formula, so the fixed cost is not double-counted.

Will this formula work in WPS Office Spreadsheets?

Yes. WPS Office Spreadsheets supports Excel formulas, including AVERAGE and mixed references, and it can open and save .xlsx workbooks.

What should I do if I get a #DIV/0! error?

Check that the divisor cells B2:D2 and B3:D3 are not all empty and that the denominator sum does not evaluate to zero. Also confirm E4 and E5 are numeric.