Shane, your quantity total / 40 is your forty's value. I'm assuming this is
how you got that number.

However, if you just sum from the beginning and divide by forty you will
have the Forty column

Assuming Shipment in A1,
C2 =INT(B2/40)
C3 =INT(SUM(B$2:B3)/40)-SUM(C$2:C2)
Fill that down to the bottom to get your forty

For the total, just ignore column C and divide Sum(B) by forty.

HTH, Bob



" wrote:

Hello,

Long time reader, first time poster. Hope I can find some insights.

Using the following sample table I want to be able to count the number
of times, and when, a total of 40 is reached in the Qty column. And
then with the remaining amount begin the count to 40 again. For
example, summing all the cells from 9.0AD to the 14.6AD row would
result in the first 40 - with 5.51 left. That 5.51 would then be added
to the 114.8 to make 120.31 - and another 3 x 40's with 0.31 added to
the 15.0AD line :

Shipment Qty 40's
9.0 AD 0.00
11.0 AD 0.00
12.0 AD 0.46
13.0 AD 2.76
13.5 AD 0.92
13.8 AD 1.38
14.1 AD 7.35
14.4 AD 19.29
14.6 AD 37.65 1
14.8 AD 114.80 3
15.0 AD 297.55
15.2 AD 1057.04
15.4 AD 1280.20
15.6 AD 927.55
15.8 AD 383.42
16.0 AD 223.62
16.2 AD 182.76
16.4 AD 54.64
4591.38 114.78

Not sure where to begin with this? Count + Sum +?

If I can clarify please advise.

Thanks,

Shane