Last Updated: 11/08/2026
If you’ve ever needed to quickly work out an average – whether it’s your monthly spending, staff wages, or household bills – LibreOffice Calc makes it easy once you know the formula pattern. In this tutorial, we walk through exactly how to calculate averages using simple SUM and division formulas, with several real-world examples along the way.
Setting Up the Spreadsheet
We start by opening a new LibreOffice Calc spreadsheet and building a simple two-column layout: Month and Cost. Using the Series Fill feature, we quickly populate the Month column from January through December, giving us 12 rows of data to work with. Each month then gets an associated cost value, which we format as currency by right-clicking the cells and selecting Format Cells.
Step 1: Calculate the Total with SUM
Before you can find an average, you need a total. Click on an empty cell below your data and use the SUM function:
=SUM(B2:B13)
LibreOffice highlights the range of values it’s calculating in blue, and pressing Enter returns the grand total for all 12 months.
Step 2: Divide the Total by the Number of Values
=B14/12
Where B14 is the cell containing your grand total. This gives you the average cost across the 12 months – a fast, reliable way to see what you’re spending on average each month.
A Quick Numeric Example
The same logic works with any list of numbers, not just monthly costs. Highlight a set of values, use the SUM tool to get the total, then divide by the count of numbers. For example, summing a set of values that total 258 and dividing by 12 gives an average of 12.5 – demonstrating the formula works the same way regardless of what the numbers represent.
Real-World Example: Averaging Multiple Expense Categories
To take things further, the tutorial builds out a more practical example: tracking electric, gas, and water bills across all 12 months.
- Copy the Month column to set up a new table for the three expense categories.
- Enter estimated monthly values for electric (£25), gas (£20), and water (~£20), adjusting for seasonal variation.
- Calculate the grand total for each category by highlighting the column and using the AutoSum tool.
- Calculate the average per category using the same total-divided-by-12 formula (e.g., =B31/12).
- Drag the formula across columns using the fill handle to quickly apply the same calculation to gas and water without retyping it.
Combining Categories for a Total Monthly Average
Once you have averages for each individual category, you can go one step further and calculate a combined average across electric, gas, and water together:
- Add up all three categories for each month (e.g., =SUM(B2:D2)) to get a monthly combined total.
- Sum that new column down all 12 months to get the annual grand total.
- Divide the annual total by 12 to get your average combined monthly cost.
In the example used in the video, the total spend across gas, water, and electric for the year came to £1,530, which – divided by 12 – gives an average monthly cost of £127.50.
Why This Matters
This simple SUM-and-divide technique isn’t just a spreadsheet trick – it’s genuinely useful for:
- Budgeting household bills like gas, electric, and water
- Tracking fuel costs for a car over time
- Calculating average staff wages or business expenses
- Any scenario where you need a quick, accurate average from a set of monthly or periodic figures
Final Thoughts
Calculating averages in LibreOffice Calc comes down to two core steps: sum your values, then divide by the count. Whether you’re averaging a single column of costs or combining multiple expense categories into one overall average, this method scales easily and can be applied to countless personal and business scenarios – from fuel bills to utility costs to monthly budgets.
