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.


"Angus" wrote:

> 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

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

"Barb Reinhardt" wrote:

> 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.
>
>
> "Angus" wrote:
>
> > 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

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?

--
Regards

Roger Govier


"Angus" <(E-Mail Removed)> wrote in message
news:EF2903A4-2170-4A7E-9707-(E-Mail Removed)...
> 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
>
> "Barb Reinhardt" wrote:
>
>> 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.
>>
>>
>> "Angus" wrote:
>>
>> > 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

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.

"Angus" wrote:

> 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
>
> "Barb Reinhardt" wrote:
>
> > 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.
> >
> >
> > "Angus" wrote:
> >
> > > 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