#CREATE AVERAGE OF PERCENTAGES IN EXCEL UPDATE#

Formulas, on the other hand, update instantly when data changes. However, one drawback is that pivot tables need to be refreshed to show the latest data. Pivot Table optionĪ pivot table would also be a good way to solve this problem, and would provide additional features.

Note: there are ways to extract unique values in older versions of Excel, but they are more complicated. This formula can then be copied down to return an average for each group. Note the criteria is no longer a spill range.

Then the formula in G5 becomes: =AVERAGEIFS(data,data,F5) Instead of using the UNIQUE function to automatically extract unique groups, the groups can be entered manually. In older versions of Excel without the UNIQUE function, the approach needs to be modified slightly. If rows are deleted from the table, the spill range contracts as needed. In all cases, the spill range represents the current list of unique groups, and the AVERAGEIFS function uses this list to calculate averages. If new rows are added that include new groups, or if existing group values are changed, these changes are captured by the UNIQUE function, which expands the spill range in F5 if needed. This is because when the percentage format is. This formula is used in Excel without the latter part (100). Lastly, the percentage of the colored cells to the total net sales will appear in cell D14. The basic percentage formula is (part/total)100. Then, press Enter and select the ‘ ’ icon in the Number group under the Home tab. After that, select cell D14 and type the formula: D13/D12. If new rows are added that refer to existing groups, the spill range returned by UNIQUE remains unchanged, and AVERAGEIFS calculates an updated set of averages. Again, select cell D13 and type the formula: Next, press Enter. The key advantage to this approach is that it responds instantly to changes in the data. The result from UNIQUE is an array with 3 values like this: Įach number in the array is the average calculated for a group. The formula in F5 is: =UNIQUE(data) // unique groups The next step is to list the unique group values in column C starting in cell F5. Therefore, the temptation of averaging percentages can provide inaccurate results. The table will now automatically expand or contract as needed when new data is added or removed. It can be observed that we have inconsistencies between the 66 (accurate calculation), and the 75 (inaccurate calculation). You can use the keyboard shortcut Control + T. In this case, all we need to do is create a new table named data with the data shown in B5:D16. One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed. Calculate averages with the AVERAGEIFS function.List unique groups with the UNIQUE function.In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps:

Create average of percentages in excel