Dividing a range in groups that add up to < a certain number

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86
I have a simple spreadsheet with years in column one, and values in the second one. I want to cluster the years in groups that add up to less than 1000 (and there is no year that has more than 1000). The first bunch of years have very small numbers, so there we'd have to find the exact amount of years that yield a number that is just under 1000 - i.e. whereby the next year would get us over that limit. And then the second group of 1000 would start with the first next year and again continue until the year that once again tips us over the limit. And so on until the last year. What is the best way to do this? Thanks!

-Stephan
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
Please provide some sample data illustrating what you want.
 

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86
I guess I may not be explaining myself very well. But so I've attached the file. Those are years and the number of articles that correspond to a search query I entered. So the first year in which there was an article that corresponded to my query was 1161, and there was just one article. I now want to download all of these, but the system only allows me to download 1000 at a time. So I want to find out an easy way to refine my search query in such a way (by selecting year ranges - e.g. from year a to year b) that I get nice a year range that contains (preferably just) under 1000 articles. So through manual trial and error I can figure that my first batch of articles should be from 1660-1941: =SUM(B4:B79) is just under 1000 whereas =SUM(B4:B79) is just over. And the next year range should be 1945-1958 (B80:B93). Sn I'm now looking for a formula that can automatically generate those year ranges that yield as close to under 1000 articles as possible. Is that clearer?
 

AliGW

.
Joined
Mar 9, 2014
Messages
3,628

ADVERTISEMENT

Many members can't or won't access file-sharing sites. The ForumTools add-in (link in my sig line) will allow you to present your data in a clear and usable format within the body of your post.
 

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86
Many members can't or won't access file-sharing sites. The ForumTools add-in (link in my sig line) will allow you to present your data in a clear and usable format within the body of your post.
Sorry. Is this better?
Excel 2016 (Windows) 64 bit
A
B
3
Year_of_PublicationARTICLE_COUNT
4
1660​
1​
5
1811​
1​
6
1828​
1​
7
1831​
1​
8
1832​
1​
9
1835​
1​
10
1837​
2​
11
1838​
1​
12
1839​
2​
13
1845​
1​
14
1846​
1​
15
1857​
2​
16
1859​
2​
17
1862​
1​
18
1863​
1​
19
1868​
2​
20
1876​
1​
21
1877​
1​
22
1878​
1​
23
1884​
2​
24
1887​
1​
25
1888​
2​
26
1890​
2​
27
1891​
2​
28
1893​
6​
29
1894​
14​
30
1895​
16​
31
1896​
26​
32
1897​
18​
33
1898​
15​
34
1899​
13​
35
1900​
10​
36
1901​
7​
37
1902​
7​
38
1903​
11​
39
1904​
3​
40
1905​
8​
41
1906​
4​
42
1907​
3​
43
1908​
10​
44
1909​
4​
45
1910​
2​
46
1911​
7​
47
1912​
4​
48
1913​
9​
49
1914​
10​
50
1915​
6​
51
1916​
17​
52
1917​
12​
53
1918​
13​
54
1919​
15​
55
1920​
7​
56
1921​
8​
57
1922​
6​
58
1923​
28​
59
1924​
21​
60
1925​
22​
61
1926​
20​
62
1927​
14​
63
1928​
16​
64
1929​
17​
65
1930​
20​
66
1931​
28​
67
1932​
36​
68
1933​
29​
69
1934​
23​
70
1935​
24​
71
1936​
38​
72
1937​
40​
73
1938​
37​
74
1939​
27​
75
1940​
28​
76
1941​
38​
77
1942​
33​
78
1943​
44​
79
1944​
62​
80
1945​
53​
81
1946​
55​
82
1947​
47​
83
1948​
61​
84
1949​
59​
85
1950​
63​
86
1951​
72​
87
1952​
86​
88
1953​
59​
89
1954​
65​
90
1955​
67​
91
1956​
81​
92
1957​
86​
93
1958​
105​
94
1959​
108​
95
1960​
107​
96
1961​
115​
97
1962​
102​
98
1963​
134​
99
1964​
160​
100
1965​
172​
101
1966​
177​
102
1967​
180​
103
1968​
211​
104
1969​
245​
105
1970​
291​
106
1971​
294​
107
1972​
337​
108
1973​
391​
109
1974​
396​
110
1975​
400​
111
1976​
473​
112
1977​
487​
113
1978​
514​
114
1979​
553​
115
1980​
554​
116
1981​
592​
117
1982​
598​
118
1983​
591​
119
1984​
628​
120
1985​
685​
121
1986​
773​
122
1987​
760​
123
1988​
836​
124
1989​
840​
125
1990​
903​
126
1991​
888​
127
1992​
1017​
128
1993​
1014​
129
1994​
1009​
130
1995​
1065​
131
1996​
1100​
132
1997​
1220​
133
1998​
1347​
134
1999​
1384​
135
2000​
1164​
136
2001​
1030​
137
2002​
991​
138
2003​
1051​
139
2004​
1038​
140
2005​
1103​
141
2006​
1106​
142
2007​
1058​
143
2008​
946​
144
2009​
898​
145
2010​
727​
146
2011​
692​
147
2012​
684​
148
2013​
115​

<tbody>
</tbody>
Sheet: data (1)

<tbody>
</tbody>
 
Last edited:

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86

ADVERTISEMENT

I am still interested in an answer to this question. Could somebody please still look into this? Much appreciated...
 

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86
Hmmm. So should I just repost this as a new question to get an answer?
 

glfiedler

Board Regular
Joined
Apr 1, 2019
Messages
67
Office Version
  1. 365
Platform
  1. Windows
I have added a running "total" column C and and "indicator" column D.
In cell C4: =B4
In cell C5: =IF((B5+C4)>1000,B5,B5+C4)
Copy C5 to the end of your data.
In cell D5: =IF(C4>C5,"BREAK","")
Copy D5 to the end of your data.
The word BREAK will be at the next year group.
The running total one row above the BREAK will be the number of downloads.
Hope this helps.
 

sdspieg

Board Regular
Joined
Dec 9, 2013
Messages
86
Wow! This is fantastic! Works like a charm (although ( had to adjust a bit to start two rows higher - but that was trivial) Thank you so much for that - you've saved me hours in my work...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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
Top