Average doesn't work in pivottable calculated field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angus - 13 Jun 2007 06:48 GMT

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

Reply to this Message

Barb Reinhardt - 13 Jun 2007 11:04 GMT

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.

> 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
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Angus - 13 Jun 2007 11:19 GMT

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

> 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
[quoted text clipped - 17 lines]
> > 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

Reply to this Message

Roger Govier - 13 Jun 2007 12:22 GMT

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?

Signature

Regards

Roger Govier

> Thanks for your quick reply.
>
[quoted text clipped - 46 lines]
>> > many I should order: =roundup(Qty/Min_Order,0)*Min_Order . Please
>> > help

Reply to this Message

Barb Reinhardt - 13 Jun 2007 12:53 GMT

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.

> Thanks for your quick reply.
>
[quoted text clipped - 38 lines]
> > > 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

Reply to this Message

Rate this thread: