Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Sum Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Sum Problem
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(B240,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, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Sum Problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count problem | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Count Problem | New Users to Excel | |||
COUNT IF (AND / OR) SUM IF PROBLEM | Excel Worksheet Functions |