1

Suppose I have six cells (A1:A6), containing the values 1, 2, 4, 8, 16 and 32.

These values form a pattern, which I can graph. I want to expand the list of values to include intermediate ones extracted from the pattern.

As the highest number in this table is 32, I need to generate a column with 32 cells, B1:B32. Each cell in B1:B32, should have the corresponding value from the curve of the original data.

For sure, B1 will be 1, and B32 will be 32, but B2:B31 should be interpolated from the A1:A6 table.

How can I do it?


Gary's Student, the values 1, 2, 4, 8, 16 and 32 (2^n) is just an example, the real numbers could be any non linear curve, like 1, 12, 19, 22, 29 and 32 for example, in Y.

I think I need to find the gradient between each Y and the next, find the integers and interpolate X for them. In a way I did it on Sunday.

The problem is that Y is not a line segmented in straight lines, in some way, later, I need to create a smooth curve transition between 1-12 and 12-19 for example. Let me explain. The six elements (X) are in fact equal distance, time, etc, when measurements are done in a physical variable (Y), voltage, level, weight, etc. Every measurement is a snapshot of the variable. The variable is changing in distance, time, etc, and it varying smoothly.

The six values of Y should not create a dent in the graph, but mid points of a smooth curve. But this is for later. Right now I want to find the other 26 intermediate missing values of X for the 32 of Y, when 6 I already know (1, 2, 3, 4, 5, 6).

Thanks.

  • I'm not quite sure what you're asking (but I'm sure I can help once I understand.) Are you trying to make a geometric series with 32 terms that starts from 1 and goes to 32? (All intermediate values of which will be irrational....) Or what? Can you post some example numbers and sample desired output? – Wildcard Oct 11 '15 at 16:09
  • This is likely to be a complex task. You might be able to extract the interpolated formula from the chart, then use e.g. jkp-ads.com/Articles/ChartAnEquation00.asp to get values – Hannu Oct 11 '15 at 17:34
  • 1
    This is essentially the same as your other question. There is no basis on which to perform interpolation. You have six values that are sequenced in ascending order. You can graph their values next to each other so it appears to be a pattern, but it isn't. It is just a collection of numbers. Each number represents a seperate category when you graph it. It could be dogs:1, apples:2, rocks:4, etc. So the fact that the numbers form a shape when you graph it doesn't mean that they actually have any relationship to each other. Interpolating between 2 and 4 might give you cats. – fixer1234 Oct 11 '15 at 17:59
  • 1
    This is a duplicate of your previous question. In order to interpolate, you need to decide on the relationship between the values. In your previous question, a 4th-order polynomial fit (or over-fit) the data; in this example, there is an exponential relationship between the values. Once YOU decide on the relationship, then it's a matter of applying the appropriate formula. For this set of data, you would insert a column of equally spaced values in rows 1..32 that go from 1..6, and then use the GROWTH function to calculate the Y values. – Ron Rosenfeld Oct 11 '15 at 19:54
  • It looks like Mokubai moved your "answer" into the question because it wasn't a solution. However, it also fundamentally changes the nature of this question. The original version of this question was answerable in a limited way because the exact equation could be determined, making it a special case. That question was answered. What this addition introduces requires a completely different approach and a very different answer. Your overall problem has a bigger picture, but we're getting there in steps. (cont'd) – fixer1234 Oct 13 '15 at 18:36
5

Gary's Student's answer is obviously what you're looking for, but it's important to understand how it's different from what you asked so that you see the underlying principle. In this question, you provided numbers that follow an obvious pattern, and you want another set of numbers to follow the same pattern. But there's an important piece missing.

1-Dimensional Data

If the only numbers you have are the six you gave us, they could mean something like this:

series 1

I can make a chart of this:

chart 1

When I stick the numbers next to each other in the original order, they form a pattern, and the pattern looks meaningful. However, the data is just numbers of different animals. I could alphabetize my list:

series 1 alphabetized

and the same data would look like this:

chart 1 alphabetized

This kind of data is just a list and nothing gives it inherent order or a relationship between the numbers. Go back to the original order and the first graph, and suppose you want to interpolate for a value of 25. It would be somewhere between hamster and goldfish.

But there's another problem. Suppose you haven't completed your pet inventory, yet, but you got this far:

series 1 short

You're missing the cats and rabbits. When you plot this, you get:

series 1 short chart

Whatever categories are there get plotted at equal spaces. In the previous examples, that gave you the shape you expected. When data is missing, there is nothing to correctly position the rest of the data on the chart.

2-Dimensional Data

There's an implied relationship that you left out in your question. If you want the data values to represent a mathematical pattern or relationship, they have to be tied to something else. You need something that varies in a defined way that your data is related to. Gary's Student introduced that in his answer:

series 2

The X values define the Y values. They don't need to be at a fixed interval, it's just a continuous variable and you can pick any X values you want. But that variable defines the position along the X axis.

series 2 chart

If you were missing the data for an X value of 3, the rest of the data would still be correctly graphed. Also, unlike the earlier example where the data was alphabetized, the sequence doesn't make a difference. The X values ensure that the data points are in the right place regardless of their order (although if you use a chart type with connecting lines, Excel will draw the lines in the sequence that the data is listed, so while the ponts are in the right place, the connecting lines wouldn't be useful).

The X and Y values give you something to interpolate. It's the relationship between them that allows you to do it. In your question, you want to add additional points along the curve. What makes a point be on the curve is an X value that defines the position where you want another point, and the Y value of the curve at that point (or vice versa).

About solutions to your question

Actually, there's an infinite number of ways to add your additional points. For example, your Y values could be the numbers 1-32. Your reaction might be that those values would just be in a straight line. It's the appropriate X values that position these Y values to be on the curve:

new intermediate data

Notice that this set of 32 points includes all of your original ones. The X values here were found by using Gary's Student's equation in reverse. All of these additional points are on your curve, just distributed differently.

new chart

So any time you have data in a pattern and want to interpolate, you need to associate it with the X values that define it. In your two questions you were essentially assuming that the X values were equally spaced, like the row numbers that Gary's Student used in his answer. That's one possible set of points. It's up to you to decide where you want the extra points.

  • 2
    Dear Fixer1234, you read my mind, since I forgot to make the original question complete. That is exactly what I am looking for. The table with 6 entries, A1:A6=1,2,3,4,5,6 and B1:B6=any non linear sequence, for the sake of the study, lets use the 2^n = 1, 2, 4, 8, 16, 32. I need to expand this A1:B6 to 32 entries, where B1:B32 is just a sequence of integer numbers 1 to 32 and A1:A32 will need to fill in the mid numbers between 1 and 6. Exactly as you show above. How to calculate the blanks in A1:A32 ? Thanks. – Wagner Lip Oct 13 '15 at 1:29
  • This is just math. If Y=2^X, then X=log(Y)/log(2). Note that we can do this because we know the equation that relates Y to X. When you talk about "any non-linear sequence", that's a whole different ballgame. I see you posted an "answer" that heads there. I'll add a comment. – fixer1234 Oct 13 '15 at 1:52
  • @WagnerLip if this answer helped u ,feel free to accept this answer which would serve as reward to the writeup :) – BlueBerry - Vignesh4303 Oct 13 '15 at 4:36
2

If you place your data in column B and in A1 through A6 enter 0 through 5, it is pretty obvious that you have an exponential curve. To test this in C1 enter =2^A1 and copy down:

enter image description here

To spread the data out as you desire, in F1 enter:

=5*(ROW()-1)/31

and in G1 enter:

=2^F1

and copy down:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.