How to Calculate an Excel Average While Including a Fixed Cost
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.

- 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.
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.
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.
Click cell E2, or the first cell where you want the calculated Month 4 value to appear.
Type =AVERAGE(B2:D2)/(AVERAGE(B$2:D$2)+AVERAGE(B$3:D$3))*(E$5-E$4) and press Enter.
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.
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.

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. 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. Locate the formula cells: Click the cell where the first Month 4 result belongs, such as E2, and select the formula bar.
- 3. Enter and fill the formula: Type the AVERAGE formula, press Enter, then drag the fill handle down to the next row.
- 4. Review and save: Confirm the results and save the file in .xlsx format or WPS format as needed.

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.




