Interpolation between Years

[mark_paul]

Hi,

How to achieve interpolated between years and get the values?

Source 1:

Year	Value
1	1.3456
2	1.45678
3	2.223456
4	2.98765
5	1.9876
6	1.32456
7	1.435678
8	2.213456
9	0.98765
10	2.123456

Target:

ID	Year_1	Interpolated_value
100	2.124	Interpolated (((2.223456-1.45678)/(3-2)) * (2.124-2) + 1.45678) =1.551847824
101	3.987	((2.98765-2.223456)/(4-3)) * (3.987-3) + 2.223456 = 2.977715478
102	6.12	Interpolated between 6 and 7
103	5.432	Interpolated between 5 and 6
104	9.765	Interpolated between 9 and 10
105	8.432	interpolated between 8 and 9
106	7	1.435678
107	4.378	interpolated between 4 and 5

Thanks,

Mark

[patrick_digan]

@mark_paul I've attached an example using the ceil and floor function to join the 2 data points and then calculate the output.

[mark_paul]

@patrick_digan ,

Thanks for your quick response.

what if one of the year is missing?  for example year 8 is not available in the source then need the interpolation between 7 and 9. How to achieve this?

Year	Value
1	1.3456
2	1.45678
3	2.223456
4	2.98765
5	1.9876
6	1.32456
7	1.435678
9	0.98765
10	2.123456

Thanks,

Mark

[afv2688]

Hello @mark_paul ,

Does this help?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[RolandSchubert]

Hi @mark_paul ,

it's possible to o it using Transpose and Multi-Row Formula tool. I've attached a sample workflow.

Best,

Roland

[mark_paul]

@afv2688 ,

Its not working for below source when interpolating between 7 and 9 for computing the 8.432

Year	Value
1	1.3456
2	1.45678
3	2.223456
4	2.98765
5	1.9876
6	1.32456
7	1.435678
9	0.98765
10	2.123456

Thanks,

Mark

[mark_paul]

@RolandSchubert ,

I tried with below source data its not working for interpolation between 7 and 9 when computing 8.432 value?

Year	Value
1	1.3456
2	1.45678
3	2.223456
4	2.98765
5	1.9876
6	1.32456
7	1.435678
9	0.98765
10	2.123456

Thanks,

Mark

[afv2688]

Hello @mark_paul ,

Have updated the workflow. Forgot to add something

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[afv2688]

Hello @mark_paul,

Added a nice touch to filter out all the fields that are out of the interpolation limits.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[RolandSchubert]

Should work now, if years are missing ... added some "find nearest upper/lower" calculation