Interpolation in Excel with Non-Constant Intervals
Solved
Stan111
Posted messages
8
Status
Member
-
Stan111 Posted messages 8 Status Member -
Stan111 Posted messages 8 Status Member -
Hello,
I am facing a small problem with interpolation in Excel... Before asking my question, I searched the forums and didn’t find anything.
My issue is that my interpolation needs to be done based on values with a non-constant X interval.
For example, my dataset looks like this:
x y
0 2.33
0.17 2.32
0.43 2.22
0.78 2.14
0.84 2.02
0.87 2.00
1.03 1.77
From that, I would like to interpolate values for a constant x interval, for example for x = 0/0.25/0.50/0.75/1.00/...
Some people use fairly simple formulas, but to apply them in my case, I would have to do it manually for each data point. The problem is that I must have around 250,000 rows of data...!
Has anyone encountered this before? You can treat me like a beginner; I usually do surface interpolation but never linear! If it's easier, I also have a base in R and I have access to MathLab, although I'm not familiar with it.
Thanks in advance
I am facing a small problem with interpolation in Excel... Before asking my question, I searched the forums and didn’t find anything.
My issue is that my interpolation needs to be done based on values with a non-constant X interval.
For example, my dataset looks like this:
x y
0 2.33
0.17 2.32
0.43 2.22
0.78 2.14
0.84 2.02
0.87 2.00
1.03 1.77
From that, I would like to interpolate values for a constant x interval, for example for x = 0/0.25/0.50/0.75/1.00/...
Some people use fairly simple formulas, but to apply them in my case, I would have to do it manually for each data point. The problem is that I must have around 250,000 rows of data...!
Has anyone encountered this before? You can treat me like a beginner; I usually do surface interpolation but never linear! If it's easier, I also have a base in R and I have access to MathLab, although I'm not familiar with it.
Thanks in advance
4 answers
Hello,
It is assumed that the data is sorted in x.
Excel offers functions like Trend and Forecast, but with 250,000 rows, we exceed reasonable amounts for array formulas.
If we limit ourselves to linear interpolation between two points, we need to look for each "reference" value the corresponding lower and upper x values and their corresponding y values.
https://www.cjoint.com/?3IDqnzQQopg
The chart is only there to show the divergence of the results.
It is assumed that the data is sorted in x.
Excel offers functions like Trend and Forecast, but with 250,000 rows, we exceed reasonable amounts for array formulas.
If we limit ourselves to linear interpolation between two points, we need to look for each "reference" value the corresponding lower and upper x values and their corresponding y values.
https://www.cjoint.com/?3IDqnzQQopg
The chart is only there to show the divergence of the results.
Hello tongtong,
The sorted data in x, the challenge was indeed to find the reference values since the interval in x was inconsistent. It worked with a total of 64 nested (IF function), which is the maximum allowed by Excel!
Interpolation was really what I was looking for and we used the same formula!
Thank you! :D
The sorted data in x, the challenge was indeed to find the reference values since the interval in x was inconsistent. It worked with a total of 64 nested (IF function), which is the maximum allowed by Excel!
Interpolation was really what I was looking for and we used the same formula!
Thank you! :D
Hello,
I don't really see the purpose of the 64 IFs.
With data sorted in ascending order, VLOOKUP or INDEX/MATCH find the value immediately below the criteria.
For the upper value, you just need to shift down one row since the values are sorted.
In the #1 file, these are the formulas in columns F, G, H, I.
I don't really see the purpose of the 64 IFs.
With data sorted in ascending order, VLOOKUP or INDEX/MATCH find the value immediately below the criteria.
For the upper value, you just need to shift down one row since the values are sorted.
In the #1 file, these are the formulas in columns F, G, H, I.
AH! I had used the IF statements because I had initially unified my tables. The Xs at regular intervals for which I was looking for Ys by interpolation appeared through the others, whose intervals were not constant. I had sorted everything based on the X value. Furthermore, I didn't know about the VLOOKUP and INDEX functions, which forced me to do it manually, in the form of an equation. The IF statements were therefore used to say that if there is a value in the lower or upper cell, use it to calculate the interpolation, but I had to include all possible variations because the intervals were really irregular. But thanks to these functions, it does it all by itself and it's great! :) I reach the same values but in so much less processing time!
Thank you so much!
Thank you so much!