[Solved] Average price in data pilot

[Rishi]

Hi,
I have made a data pilot table of the sales per customer. In this in the data area there are two fields,
1) Sum of Quantity
2) Sum of Total amount.

Now, I want to get the average cost per unit on a per customer basis. If I just take the average of the costs, the answer will be wrong. The actual value is = (Sum of Quantity) / (Sum of Total Amount). How do I display this on the data pilot table?

Thanks in advance.

[squenson]

I think you cannot get this information directly in the data pilot table. I believe that the reason is that data pilot goes through the list of all records only once and collects information attribute by attribute and cannot make first a sum of all the quantities, then a sum of all the amounts, then the division.

A way to overcome the problem:
Create in your initial list three additional columns that will contain the total quantity per customer and total amount per customer. If your range is A2:C1000 (A=Customer, B=quantity, C=Amount), use a formula in D2: =SUMIF($A$2:$A$1000; $A2; $B$2:$B$1000), in E2: =SUMIF($A$2:$A$1000; $A2; $C$2:$C$1000) and in F2: =IF(D2=0;0;E2/D2). Select the range D2:F2 and copy it down your list up to row 1000. You can then use the column F2 in your data pilot.

[Rishi]

It worked. I got the average price per user on an annual basis.

I also have a field flor the month. This would be in the column field. How would I get the average prrice on a monthly basis?

[squenson]

When you have more than one condition, SUMIF runs short... but SUMPRODUCT comes to the rescue!

SUMPRODUCT works with a set of numbers in columns of the same size. It first multiplies all the numbers from the first row, then adds to them the product of all the numbers of the second row, etc (hence the name SUMPRODUCT). The trick is that you can put a condition that returns 1 (TRUE) or 0 (FALSE), and as you know, multiplying by 0 gives 0, and adding 0 does not change the result.

Having said that, you can have a new column that calculates the total quantity for the month (tip: I always use the format YYYYMM when I deal with dates, like 200804 as a whole number for April 2008, it is very useful):

If your range is A2:D1000 (A=Customer, B=quantity, C=Amount, D=YYYYMM), use a formula in E2: =SUMPRODUCT($A$2:$A$1000=$A2; $B$2:$B$1000; $D$2:$D$1000=$D2), then copy it down your list up to row 1000. Do the same for the Amounts and then divide the two!

[Rishi]

I made a new column for month using the month() function. Using that column the entered data gave me monthwise average price.

However, when the totals are taken, in the last column the annual price is just the mean of the monthly prices. It seems that both the monthly and the annual prices cannot be shown at the same time.

[squenson]

If you use the MONTH() function, consider the case when you will have entries like 01-Apr-2008 and 01-Apr-2009, both will be summarized together. I prefer to use YEAR(<date>)*100+MONTH(<date>) it is safer in my opinion.

As far as the annual total is concerned, do the same as for the month, but this time with YEAR() in a new column.

[Rishi]

The list only contains data for one year. So the month() function works for this spreadsheet. The YYYYMM format would be useful for multiyear data formats.

I also modified the column to date(2007,month(a4),1). Then I changed the view format to get Jan,feb, Mar and so on.

The in the totals column and row the price is still wrong, but I guess that I will have to live with that.

Thanks for all the help.