MS Excel: Change Sum to Average or Count in Pivot
Pivot Tables is a very good tool in excel which helps to easily analyze and consolidate entire data into a reporting format or in a structural way. While creating a pivot table the fields that you drag into the Values section are by default either sum or count.
Now in case we have around 50 fields to be dragged into the values section which in turn will come as different columns. Now in case we want to change the data columns from Sum to Count or from Sum to Average then we would need to do it manually as shown below for all the fields one by one.
Now doing this can be a very tedious task and also very time consuming. Instead we are providing you with a add-in which can help you to do all of this with just one click. Below is how to do it. Also this addin comes with a lot of many other options such as Count all, Max All, Product All.
All of it you can use at your convenience.
You can download this add-in from here
Now take a leap in speeding up your working with pivots in excel.
Now in case we have around 50 fields to be dragged into the values section which in turn will come as different columns. Now in case we want to change the data columns from Sum to Count or from Sum to Average then we would need to do it manually as shown below for all the fields one by one.
Now doing this can be a very tedious task and also very time consuming. Instead we are providing you with a add-in which can help you to do all of this with just one click. Below is how to do it. Also this addin comes with a lot of many other options such as Count all, Max All, Product All.
All of it you can use at your convenience.
You can download this add-in from here
Now take a leap in speeding up your working with pivots in excel.
Average In MS Excel 2007
Let us learn how to calculate an average of any data. For this consider the table below:
As we see above we have data for 5 batsmen playing cricket along with the scores in every match. So the consolidated score for 3 matches is available. Now in order to calculate what is the average score for each batsman is, here is how we go about calculating:
Put the =average formula and select the range of which you need to take an average. Now press enter and we arrive at the average score for the first batsman as below:
As we can see above the average score of 1st Batsman is 50.3 runs. Now in order to calculate for the remaining, just copy & paste the same formula.
Here you go. Now we have arrived at the average of the entire list of batsmen. We illustrated above how to calculate row wise, we can do the same column wise too (refer below):
And then you can copy and paste the same formula in rest of the columns.
Now you can easily calculate the average of any given data.
As we see above we have data for 5 batsmen playing cricket along with the scores in every match. So the consolidated score for 3 matches is available. Now in order to calculate what is the average score for each batsman is, here is how we go about calculating:
Put the =average formula and select the range of which you need to take an average. Now press enter and we arrive at the average score for the first batsman as below:
As we can see above the average score of 1st Batsman is 50.3 runs. Now in order to calculate for the remaining, just copy & paste the same formula.
Here you go. Now we have arrived at the average of the entire list of batsmen. We illustrated above how to calculate row wise, we can do the same column wise too (refer below):
And then you can copy and paste the same formula in rest of the columns.
Now you can easily calculate the average of any given data.
Listed/Ranked:
The Tech Directory
Cluster Maps