PiVot Table - Calculated field

L

Lucien

Board Regular

Joined

Oct 16, 2002

Messages

131

• Dec 4, 2002

• #2

Hi,
Did you tried the standard avarage funtion for a pivot table??. For example you drag a row name into "data" in the layoutwizard.
You can dubble click in the data, for example the sum of something and change it in average.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Dec 4, 2002

• #3

'Avg Qty/PO' is a Calculated Field defined as...

=Qty/PO

Book1
	A	B	C	D	E	F	G	H	I	J
1	Country	PO	Qty			Data
2	AUS	1	10		Country	CountofPO	SumofQty	AvgQty/PO
3	US	2	50		AUS	3	110	13.75
4	AUS	3	80		US	2	95	13.57142857
5	AUS	4	20		GrandTotal	5	205	13.66666667
6	US	5	45
7
8
Sheet1

This message was edited by Mark W. on 2002-12-04 10:52

 

Upvote 0

W

William

New Member

Joined

Nov 16, 2002

Messages

6

• Dec 4, 2002

• #4

Thanks Mark,

but this doesn't give me the correct calculation.
eg for Aus Av= 110/3 = 36.66 but your answer = 13.75

Regards,

William

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 4, 2002

• #5

On 2002-12-04 22:23, William wrote:
Thanks Mark,

but this doesn't give me the correct calculation.
eg for Aus Av= 110/3 = 36.66 but your answer = 13.75

Regards,

William

Click to expand...

Hi William:

You may have to delineate each of the POs as a single (one) PO

see the following simulation ...

y021204h1.xls
	A	B	C	D	E	F	G
1	Country	PO	Qty		Country	Data	Total
2	Aus	1	10		Aus	SumofQty	110
3	US	1	50			CountofPO	3
4	AUS	1	80			SumofAvgQtyPerPO	36.67
5	AUS	1	20		US	SumofQty	95
6	US	1	45			CountofPO	2
7						SumofAvgQtyPerPO	47.5
8					TotalSumofQty		205
9					TotalCountofPO		5
10					TotalSumofAvgQtyPerPO		41
Sheet2

</SPAN>

Does it help?

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Dec 5, 2002

• #6

On 2002-12-04 22:23, William wrote:
Thanks Mark,

but this doesn't give me the correct calculation.
eg for Aus Av= 110/3 = 36.66 but your answer = 13.75

Regards,

William

Click to expand...

William, it's always helpful to provide the answer you're seeking in your original post. I merely implemented your arithmetic expression, qty/PO, which suggested that the 'PO' field was a count rather than an id.

So... why didn't you use the suggestion provided by Lucien. A Calculated Field is not necessary.

 

Upvote 0

W

William

New Member

Joined

Nov 16, 2002

Messages

6

• Dec 5, 2002

• #7

Hello Everybody,

Thanks Lucien, Yogi and Mark. All your proposals work. I guess I was blinded/obsessed by trying to find a solution through the calculated Field option, while indeed the solution was right at hand in the standard pivot table functionality.

Regards,

William

 

Upvote 0