Average doesn't work in pivottable calculated field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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?
 
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.
 
Back
Top