

This can easily be done by clicking into the reference itself and then pressing the Function key and F4. Let’s click into the cell D3 and ‘pin’ the reference to the first point of the range – C3. So, we need to anchor the references to the cells. To ensure the function calculates the correct value in each row, we need to make sure that the formula refers to the same range in every instance.

This, of course, is something that needs to be fixed. This is because as we copied the formula, the cell references of the range moved with each row.įor example, here in row 3, we’ve got the specified cell range C3 to C14, but in row 4, the range shifted down by one, so we’ve got C4 to C15. However, if we copy the function as it is, every month shows a different number, not the correct average value. Now we need to copy the function to every row in this column, so that the average could appear as a line through every month in the chart. Here the range will be all entries in the column Sales.Īnd we’ll close the brackets to see the result. Click on the suggested function and select all the data from which we’re going to calculate the average. We’re also going to adjust the formatting of the column to make it look like the rest of the table.Ĭlick into the first row of the column Average and calculate the average value by entering the equal sign and typing in AVERAGE. Let’s insert a new column right next to the column with sales and name it ‘Average’. If we need to show the average value in a chart, we need to calculate it first. 4 How to Change the Way the Average Data Is Displayed How to Calculate Average
