Average doesn't work in pivottable calculated field

A

Angus

Eg, I use following data to make a pivot table, the minimun order for product
A is 100 and I got total 23+45+70=138 pcs of product A on hand, So I should
order 200 (minimum multiple of 100 that larger than 138).

Product Min_Order Qty
A 100 23
A 100 45
A 100 70

However, in calculated field of pivot table, no matter I use
=average(Min_Order) or =min(Min_Order), I will receive 300 as minimum order
that I cannot use Min_Order (should be 100) in my formula to calculate how
many I should order: =roundup(Qty/Min_Order,0)*Min_Order . Please help
 
B

Barb Reinhardt

Is it possible that you are trying to calculate the average for product A
when you have other products in your table with a higher min order? You
could do something ike this:

=AVERAGE(if(Product="A",MIN_ORDER))

Commit with CTRL SHIFT ENTER.
 
A

Angus

Thanks for your quick reply.

In fact I got hundreds of products but each of them has the same min_order,
so that my raw data is like:

Product Min_Order Qty
A 100 23
A 100 45
A 100 70
B 75 22
B 75 39
C 120 17
C 120 24

And I want to make my pivot table like this:

Product Min_Order Order_Qty
A 100 200
B 75 75
C 120 120
 
R

Roger Govier

Hi Angus

I explained why this won't work as a calculated field in a Pivot Table,
when I replied to your reply in worksheet functions yesterday.
I gave you a solution, by adding an extra column to your source data.
Did this not work?
 
B

Barb Reinhardt

Based upon the new information, my response probably won't work. It would
be helpful if this was only posted once. I generally don't spend time
answering questions that someone else has already answered.
 
Top