percentage increases over time

Hi. I have a list of over 2000 cities, and I need to determine percentage increases of population over each census (going back to 1860). The first percentage needs to be when the population first exceeds 800 citizens, and the percentage increase after 800 (not the previous census). After that, I need census after census. Unfortunately, since all of these cities exceeded the minimum marker at different census, I'm not sure what I can do to have the first formula be one thing and then everything after that to be a different formula.

To better illustrate what I'm looking for, I tried to mimic a small portion of the table I have.

	A	B	C	D	E
1	City	1990	2000	2010	2020
2	City A	244	438	982	1,001
3	City B	7,000	7,943	7,543	6,894
4	City C	699	850	932	955

So using the above table to find the initial increase over 800 people and then the census over census increase, I think it would look something like this:

First formula: (c2-800)/800
All subsequent formulas: (d2-c2)/c2

Is there a way I could create one formula and fill it down for all cities, or would I have to manually look at each row and type the first formula when the population exceeds 800, and then the second formula for everything after? I hope there is a way I can resolve this simply. Otherwise, it will take days to manually go city by city.

Thanks in advance!

I'm not clear on what you want the results to look like but it seems to be that it would have to be another table parallel to the first one. Please check to see if these the results you would want from the sample you provided. You can't calculate a delta for the first year's values. Do you want that to be the value - 800 even if there are no prior values?

The formula shown here can be copied to the right and down as many rows and columns needed, but the initial formula has to be aligned with your data layout.

Values as displayed

	A	B	C	D	E	F	G	H	I	J
1	City	1990	2000	2010	2020		1990	2000	2010	2020
2	City A	244	438	982	1,001				-45.3%	1.9%
3	City B	7,000	7,943	7,543	6,894			13.5%	-5.0%	-8.6%
4	City C	699	850	932	955			-12.6%	9.6%	2.5%

Underlying formulas

	A	B	C	D	E	F	G	H	I	J
1	City	1990	2000	2010	2020		=B1	=C1	=D1	=E1
2	City A	244	438	982	1,001		=IF(B2>800,IF(ISTEXT(A2),"",IF(A2>800,(B2-A2)/A2,(A2-800)/800)),"")	=IF(C2>800,IF(ISTEXT(B2),"",IF(B2>800,(C2-B2)/B2,(B2-800)/800)),"")	=IF(D2>800,IF(ISTEXT(C2),"",IF(C2>800,(D2-C2)/C2,(C2-800)/800)),"")	=IF(E2>800,IF(ISTEXT(D2),"",IF(D2>800,(E2-D2)/D2,(D2-800)/800)),"")
3	City B	7,000	7,943	7,543	6,894		=IF(B3>800,IF(ISTEXT(A3),"",IF(A3>800,(B3-A3)/A3,(A3-800)/800)),"")	=IF(C3>800,IF(ISTEXT(B3),"",IF(B3>800,(C3-B3)/B3,(B3-800)/800)),"")	=IF(D3>800,IF(ISTEXT(C3),"",IF(C3>800,(D3-C3)/C3,(C3-800)/800)),"")	=IF(E3>800,IF(ISTEXT(D3),"",IF(D3>800,(E3-D3)/D3,(D3-800)/800)),"")
4	City C	699	850	932	955		=IF(B4>800,IF(ISTEXT(A4),"",IF(A4>800,(B4-A4)/A4,(A4-800)/800)),"")	=IF(C4>800,IF(ISTEXT(B4),"",IF(B4>800,(C4-B4)/B4,(B4-800)/800)),"")	=IF(D4>800,IF(ISTEXT(C4),"",IF(C4>800,(D4-C4)/C4,(C4-800)/800)),"")	=IF(E4>800,IF(ISTEXT(D4),"",IF(D4>800,(E4-D4)/D4,(D4-800)/800)),"")

@6StringJazzer

Thanks. This almost works. The year over year formulas work great, but the first time it exceeds 800, It's not showing the result of (a1-800)/800, but rather the result is -1. I attached a spreadsheet if that helps.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

Are you on a mobile device?

I'll do it for you this time.