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

sdspieg · Jan 8, 2017

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

AliGW · Jan 8, 2017

Please provide some sample data illustrating what you want.

sdspieg · Jan 8, 2017

AliGW said:
Please provide some sample data illustrating what you want.

dl=0https://www.dropbox.com/s/a4twp9jb0339i3q/data (1).csv?dl=0

sdspieg · Jan 9, 2017

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 · Jan 9, 2017

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 · Jan 9, 2017

AliGW said:
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_Publication	ARTICLE_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

Sheet: data (1)

sdspieg · May 8, 2019

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

sdspieg · May 11, 2019

Hmmm. So should I just repost this as a new question to get an answer?

glfiedler · May 11, 2019

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 · May 20, 2019

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...

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

sdspieg

Board Regular

Excel Facts

AliGW

Banned

sdspieg

Board Regular

sdspieg

Board Regular

AliGW

Banned

sdspieg

Board Regular

sdspieg

Board Regular

sdspieg

Board Regular

glfiedler

Board Regular

sdspieg

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

Excel Facts

Banned

Board Regular

Board Regular

Banned

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock