JeremyBowyer
New Member
- Joined
- Jan 20, 2014
- Messages
- 2
Hi guys, I've been lurking for a while and have gotten a lot of valuable information from the advice given by you folks, so thanks for that.
I have what I expect will be a simple problem (if there's a way to solve it). First off I'm working in Excel 2013 64-bit. Basically I'm going to have large amounts of data with gaps that I need to fill in using what I know from the data I do have. That's not very clear so let me give you an example.
My data looks like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]
I want it to look like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD]4[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD]6[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD]7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD]9[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD]12[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD]13[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD]14[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD]17[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD]18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD]19[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]
I'm only using the red text to indicate what was added; I don't need any help formatting the text, I know how to do that much.
Anyway, in other words I want to fill in the missing gaps as if that data increased/decreased linearly over that time period. I know how to calculate it manually, but I was wondering if there was a way to do it en masse automatically.
Any help will be appreciated.
I have what I expect will be a simple problem (if there's a way to solve it). First off I'm working in Excel 2013 64-bit. Basically I'm going to have large amounts of data with gaps that I need to fill in using what I know from the data I do have. That's not very clear so let me give you an example.
My data looks like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]
I want it to look like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Country[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1901[/TD]
[TD]Germany[/TD]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1902[/TD]
[TD]Germany[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1903[/TD]
[TD]Germany[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1904[/TD]
[TD]Germany[/TD]
[TD]4[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1905[/TD]
[TD]Germany[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1906[/TD]
[TD]Germany[/TD]
[TD]6[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]1907[/TD]
[TD]Germany[/TD]
[TD]7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1908[/TD]
[TD]Germany[/TD]
[TD]8[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1909[/TD]
[TD]Germany[/TD]
[TD]9[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1910[/TD]
[TD]Germany[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1911[/TD]
[TD]Germany[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1912[/TD]
[TD]Germany[/TD]
[TD]12[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Germany[/TD]
[TD]13[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Germany[/TD]
[TD]14[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1915[/TD]
[TD]Germany[/TD]
[TD]15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]1916[/TD]
[TD]Germany[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1917[/TD]
[TD]Germany[/TD]
[TD]17[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]1918[/TD]
[TD]Germany[/TD]
[TD]18[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1919[/TD]
[TD]Germany[/TD]
[TD]19[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]1920[/TD]
[TD]Germany[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]
I'm only using the red text to indicate what was added; I don't need any help formatting the text, I know how to do that much.

Anyway, in other words I want to fill in the missing gaps as if that data increased/decreased linearly over that time period. I know how to calculate it manually, but I was wondering if there was a way to do it en masse automatically.
Any help will be appreciated.