Find solution
How is net sales calculated?

What is the best way to calculate average sales per state using a calculated filed in an Excel Pivot table?

  • I have an excel table with a sales by salesperson and state. I want to know the average sale in each state. I can easily calculate number of sales per state and the total sales - but I can't create a calculate field that will divide these two together to give me average sale per state.

  • Answer:

    Not sure how to do this in excel but this tool will do it for you. http://www.hellodata.me Just upload the data and it creates dynamic visuals of your data. You can click on "sum" or "average" at the bottom corner of the chart, should get you to your answer.

Monica Lewis at Quora Visit the source

Was this solution helpful to you?

Other answers

There is another tool, esCalc, which is more convenient for such calculation, just import the Excel files into it , calculate and export to Excel files again. Just Group by state, "avg" for the sales data of each state, after grouping, the average data will be calculated, and you  just need to enter once, the other states' data will automatically calculated as well, don't need to copy the formula one by on as it is done in Excel.

Davina May

Create one column as Count of sales and put 1 in all cells till last record. From calculated field put No of Sales/Count of Sales Put State in Row data in pvot and calculated field in Data.

Harshal Gajare

Put sales by state in the VALUES area and change it from being Sum of ... to being Average of ... using the Value Field Settings. I know you asked about calculated fields but why do that when a) you don't need to b) if your database is big and complex, your calculated field calculations could be wrong!

Duncan Williamson

Jack Mardack

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.