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

Hi Shane

I'm not sure if your sample table reflects your example you wrote in
words, because i get a different result, but following formula should
do what you want.
I assumed you have row 1 as titlerow and start with A2, so you put
following formula in C2 (the 40's column, first data cell):
=IF(B2>40,FLOOR(B2/40,1),"")
and for cells C3 and downwards:
=IF(SUM($B$2:B3)-SUM($C$2:C2)*40>=40,FLOOR((SUM($B$2:B3)-SUM($C
$2:C2)*40)/40,1),"")

maybe someone can shorten it, but it works fine with me.

Cheers
Carlo

On Jan 18, 9:38*am, Day4Purchas...@gmail.com 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

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



"(E-Mail Removed)" 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
>