Rolling 5 Day Totals for Specific Numbers

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
190
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

I have a spreadsheet that contains numeric product IDs in column A, days in column B, number of orders in column C, returns in column D, cancellations in column E and debts in column F. I have included an extract for reference.

I am trying to come up with a formula for a rolling 5 day totals of net order numbers for each product.

To do this I need to look at column A for all products with the same ID, find the lowest 5 numbers for that product in column B (which may start from any number between 1 and 120 and may not be sequential), sum the corresponding numbers in column C and deduct the numbers in columns D, E and F. I then want to do the same for the lowest 2-6, 3-7, 4-8, etc until there are no more days for that product.

Is there a formula I can use to do this?

Thanks,

Rob

Book1
ABCDEF
1productIdcDayordersreturnscancelsdebts
21031433930000
31031433941010
41031433961001
51031433972000
61031433984000
71031433992000
810314331001010
910314331052000
1010314331060000
111031435771000
121031435824400
131031435831100
141031435851000
151031435871010
161031435890000
171031435901000
181031435914040
191031435920000
201031435932000
211031435942000
221031435951000
2310314351006000
2410314351014000
2510314351020000
2610314351092000
2710314351102000
2810314351112000
2910314351126000
3010314351131000
3110314351160000
3210314351170000
331031665400000
341031665411000
351031665480000
361031665510000
371031665522000
381031665534000
391031665541000
401031665552000
411031665560000
421031665571000
431031665591000
441031665642000
451031665652000
461031665661000
471031665672000
481031665680000
491031665690000
501031665704000
511031665710000
521031665721000
531031665730000
541031665742000
551031665751000
561031665761001
571031665771000
581031665811000
591031665831000
601031665850000
6110348271021000
6210348271030000
6310348271040000
6410348271051000
6510348271091000
6610348271124000
6710348271132000
6810348271146000
6910348271170000
7010348271180000
711034829850000
721034829860000
731068300394000
741068300402000
751068300410000
761068300420000
771068300441000
781068300474000
791068300480000
801068300501000
811068300532000
821068300541000
831068300551000
841068300582000
851068300591000
861068300626000
871068300631000
881068300651000
891068300712000
901068300721000
911068300731000
921068300764000
931068300770000
941068300781000
951068300792000
961068300806000
971068300810000
981068300821000
991068300851001
1001068300852000
1011068300860000
1021068300871000
1031068300911000
1041068300974000
1051068300981000
10610683001010000
10710683001023000
10810683001031010
10910683001052000
11010683001060000
11110683001070000
11210683001080000
1131068310320000
1141068310331000
1151068310356000
1161068310364000
1171068310401000
1181068310450000
1191068310461000
1201068310491000
1211068310522000
1221068310522000
1231068310530000
1241068310544000
1251068310561000
1261068310580000
1271068310590000
1281068310606000
1291068310624000
1301068310634000
1311068310643000
1321068310651001
1331068310661000
1341068310670000
1351068318411000
1361068318461000
1371068318502000
1381068318510000
1391068318520000
1401068318536000
1411068318540000
1421068318561000
1431068318584000
1441068318591000
1451068318610000
1461068318624000
1471068318631001
1481068318632000
1491068318644000
1501068318651000
1511068318681000
1521068318710000
1531068318720000
1541068324401000
1551068324421000
1561068324431001
1571068324430000
1581068324444000
1591068324452000
1601068324461000
1611068324483000
1621068324490000
1631068324501000
1641068324534000
1651068324541000
1661068324551000
1671068324590000
1681068324601000
1691068324611000
1701068324631000
1711068324651000
1721068324701000
1731068324730000
1741068324741000
1751068324771000
1761068324801000
1771068324841000
1781068324851000
1791068324871000
1801068324901000
1811068324912000
1821068324920000
1831068324932000
1841068324930000
1851068324941000
1861068324991000
18710683241011000
18810683241031000
18910683241054000
19010683241061001
19110683241060000
19210683241071000
1931068346350000
1941068346360000
1951068346391000
1961068346441000
1971068346462000
1981068346470000
1991068351500000
2001068351511000
2011068351540000
2021068351580000
2031068373371000
2041068373401000
2051068373421000
2061068373431000
2071068373450000
2081068373461000
2091068373481000
2101068373493000
2111068373531000
2121068373561000
2131068373581000
2141068373601000
2151068373611000
2161068373631000
2171068373652000
2181068373661000
2191068373671000
2201068373743000
2211068373761010
2221068373784000
2231068373824000
2241068373831000
2251068373871000
2261068373922000
2271068373932000
2281068373941000
2291068373992000
23010683731004000
23110683731011000
23210683731032000
23310683731042000
23410683731051000
23510683731071000
23610683731096000
23710683731100000
23810683731114000
23910683731121000
24010683731154000
24110683731162000
24210683731171000
24310683731184000
24410683731196000
24510683731202000
2461068378120000
2471068378160000
2481068378176000
2491068378181000
2501068378191000
2511068378250000
2521068382430000
2531068382440000
2541068382450000
2551068382461000
2561068382472000
2571068382480000
2581068382501000
2591068382561000
2601068382601000
2611068382614000
2621068382621000
2631068382631000
2641068382661000
2651068382676000
2661068382680000
2671068382691000
2681068382774000
2691068382780000
2701068382821000
2711068382854000
2721068382861000
2731068382880000
2741068382891000
2751068382911000
2761068382940000
2771068382954000
2781068382964000
2791068382981000
28010683821022000
28110683821035005
28210683821046000
28310683821050000
28410683821062000
28510683821070000
28610683821081000
28710683821090000
28810683821102000
28910683821111000
29010683821142000
29110683821162000
29210683821170000
2931068401490000
2941068401500000
2951068401516000
2961068401521000
2971068401541000
2981068401551000
2991068401591000
3001068401612000
3011068401621000
3021068401660000
3031068401734000
3041068401741000
3051068401762000
3061068401771000
3071068401801001
3081068401802000
3091068401810000
3101068411410000
3111068411420000
3121068411460000
3131068411472000
3141068411482000
3151068411510000
3161068411521000
3171068411541000
3181068411551000
3191068411571000
3201068411591000
3211068411622000
3221068411631000
3231068411672000
3241068411681000
3251068411700000
3261068411710000
3271068411720000
3281068411750000
3291068411760000
3301068411771100
3311068411800000
3321068411816000
3331068411820000
3341068411834000
3351068411841000
3361068411854000
3371068411861000
3381068411884000
3391068411891000
3401068411910000
3411068411920000
3421068411930000
3431068411941000
3441068411986000
3451068411992000
34610684111001000
34710684111021000
34810684111031000
34910684111051001
35010684111054000
3511068421411000
3521068421431000
3531068421470000
3541068421481000
3551068421491000
3561068421512000
3571068421521000
3581068421541000
3591068421551000
3601068421571000
3611068421581001
3621068421594000
3631068421600000
3641068421631000
3651068421651000
3661068421681000
3671068421701000
3681068421746000
3691068421751000
3701068421772000
3711068421781000
3721068421791000
3731068421811000
3741068421831000
3751068421852000
3761068421860000
3771068421870000
3781068421881000
3791068421901000
3801068421914000
3811068421921000
3821068421961000
3831068421971000
3841068421996000
38510684211001000
38610684211041000
38710684211064000
38810684211070000
38910684211081000
39010684211090000
39110684211101000
39210684211132000
39310684211140000
3941072317601000
3951072317611000
3961072317651000
3971072317671000
3981072317691000
3991072317720000
4001072317731001
4011072317740000
4021072318250000
4031072318260000
4041072318270000
4051072318280000
4061072318291000
4071072318320000
4081072318330000
4091072318344000
4101072318350000
4111072318361000
4121072318372000
4131072318381000
4141072318423000
4151072318430000
4161072318441000
4171072318482000
4181072318501000
4191072318534000
4201072318541000
4211072318551000
4221072318570000
4231072318581000
4241072318621001
4251072318631000
4261072318670000
4271072318680000
4281072318691000
4291072318760000
4301072318771000
4311072318791000
4321072318811000
4331072318840000
4341072318870000
4351072318882200
4361072318891000
4371072318911000
4381072318940000
4391072318951000
4401072318971000
44110723181000000
44210723181011001
44310723181012000
44410723181020000
44510723181061001
44610723181064000
44710723181070000
44810723181082000
44910723181101000
45010723181142000
45110723181172000
45210723181181000
45310723181200000
4541072319661000
4551072319670000
4561072319681000
4571072319720000
4581072321751000
4591072321770000
4601072321780000
4611072321801000
4621072321840000
463108259235000
464108259274000
465108259280000
466108259294000
4671082592100000
4681082592110000
4691082592120000
4701082592200000
4711082592210000
4721082592226000
4731082592236000
4741082592240000
4751082592291000
Sheet1
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top