Strange calculation in PivotTable

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

Guest

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
 
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)
 
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.
 
Hi Angus

I get the same result of 140 or 200 both in cells on the sheet and in a
calculated filed within the PT.
I am using XL2003

Can you give an example of your source data?
 
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
 
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)))
 
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(AVERAGE(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
 
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.
 
But I have 4 orders for product A and the total quantity should be
(20+24+30+1548)=1622, that's why my order to supplier should be 1656.

And pivottable calculates the result as 1728 instead of 1656.
 
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
 
Angus said:
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
Click to expand...
 
Hi Angus

I now see your problem with multiple orders for the same person.
The problem is, that the Calculated field is doing its calculation on
the Sum of Quantity and the Sum of Min_Order
Basically, it is doing =ROUNDUP(1622/288,0)*288 which does equal 1728.

Unfortunately there is no way of changing this behaviour, even setting
the Min_Quant field to average instead of Sum, will not change the
methodology used by the PT for calculated fields.

The only way around it would be to add another column to your source
data.
Assuming the source is sorted by Customer, then in the new column use
the following
=IF(COUNTIF($A$2:$A$5,A2)>COUNTIF($A$2:A2,A2),"",
ROUND(SUMIF($A$2:$A$5,A2,$B$2:$B$5)/C2,0)*C2)

This will put the Total Minimum order against the last row found for
that Customer.
Add this column to your PT, and on the Total row for each customer you
will see this value
 
I forgot to add

Copy the formula down the new column you have added to the source data.
Don't forget to alter the source range for the PT to now include the new
column.
 
Sorry Roger I miss your reply.

The formula doesn't work, but the result is very similar to what is given by
pivot table. eg, when min order is 200, quantity is 1230, your formula in
pivot table gives 1800.
 
I found out why it doesn't work, in fact the formula is good but my data and
presentation is more complex. My raw data is like following:

Product OrderMonth Status MOQ Quantity
A August Forecast 200 23
A August Forecast 200 74
A July FirmOrder 200 55
A July FirmOrder 200 30

And in my pivot table, I only count the quantity of FirmOrder, like this

FirmOrder/OrderMonth(column field)
Product (row field) MOQ(data field)
 
Hi Angus

I don't think you are understanding me.
I am not saying to add any formula to a calculated field in the Pivot
table. That will not work.
Remove all calculated fields from your PT.

What I said, was to add a further column to your source data, and use
the formula I posted in that column.
Copy the formula down the column.
Include that new column in your PT, and that new field will provide your
answer.
 
Roger I add one column for qty for firm order and make data sorted by status
(forecast/ firm order) and product, and apply your formula again. Now it
works perfectly. Thanks very much.
 
Hi Angus

You're very welcome. Glad it worked out for you.
Thanks for the feedback to let us know it worked.
 
Back
Top