ExcelBanter - Strange calculation in PivotTable

"Angus" wrote:

I found out the problem came from MOQ, when I have 4 lines in data for same
product, the MOQ became the total of 4 lines, in this case 288. I try to use
average(MOQ) and min(MOQ) but the results are still 288, but not 72.

How do I get 72 in this case?

Product MOQ QTY
productA 72 20
ProductA 72 1548
ProductA 72 24
ProductA 72 30

"Roger Govier" wrote:

Hi Angus

The formula
=MAX(Min_Order_Quantity,ROUNDUP(Quantity/Min_Order_Quantity,0)*Min_Order_Quantity)
produces the correct result either in a cell on the sheet or in the
Pivot Table.

The result for QTY1548 comes out as 1584, which is the smallest multiple
of 72 that exceeds 1548, not 1656 as you have written.

--
Regards

Roger Govier

"Angus" wrote in message
...
I also try following formula, but still doesn't work:

=MAX(AVERAGE(Min_order_quantity),ROUNDUP(Quantity/ROUND(AVERAGE(Min_order_quantity),0),0)*ROUND(AVER AGE(Min_order_quantity),0))

For example, for 4 lines of same product

Product MOQ QTY
productA 72 20
ProductA 72 1548
ProductA 72 24
ProductA 72 30

The calculated field of above formula will get 1728, instead of
minimun
multiple of 72 as 1656

"Angus" wrote:

I try what bj suggested and found out the issue:

For example, when I have 3 lines of order for the same product:

Product MOQ QTY
product A 30 40
Product A 30 30
product A 30 10

The sum of qty is 80 and MOQ is 120, that's why my calculation is 120
instead of 90 (minimun multiple of 30).

However i change my formula to following but the result is same, what
should
I do?

=max(( AVERAGE(Min_order_quantity),roundup( Quantity/
AVERAGE(Min_order_quantity),0)* AVERAGE(Min_order_quantity)))

and

=max(( MIN(Min_order_quantity),roundup( Quantity/
MIN(Min_order_quantity),0)* MIN(Min_order_quantity)))

"bj" wrote:

what happens when you enter the equations
=MOQ=firm order
=firm order-MOQ

I assume firm order is the same as Quantity in the equation you
described
initially

"Angus" wrote:

It doesn't work. Still the same. But when it's not pivottable but
a normal
cell the calculation is good.

The data is controlled by validation that only whole number
between 0 to
99999 is allowed.

"bj" wrote:

the problem might be that the calculation in the pivot table
does not exactly
equal 200.
the round up of 200.0000001/200.0000000 is 2
try
=max( Min_order_quantity,roundup( round(Quantity,0)/
round(Min_order_quantity,0),0)* Min_order_quantity)

"Angus" wrote:

I got minium order quantity and firm order quantity in a
pivottable, and add
a calculated formula for order quantity which should be a
least multiple of
minium order quantity, the formula is as followed:

=max( Min_order_quantity,roundup( Quantity/
Min_order_quantity,0)*
Min_order_quantity)

If MOQ is 70 and firm order is 90, then order quantity should
be 140;
however, the result is 400 when MOQ and firm order is 200
why?

When I put the same formula in a normal cell then it is
correct: 200

Please help