# Formula to find missing numbers in sequence

I'm not a mathematician, so I don't know the terminology.

I'm working in excel and I need to create a formula to fill in a sequence of numbers, where I know the beginning and end of the sequence, and the numbers in between are evenly spaced.

Basically this means that the first number (always lowest) is 0% and the highest number (always highest) is 100%, and I need to get the numbers in between.

For example with 3 .. .. .. 7, that would be 3 4 5 6 7.
Another example: 5 .. .. .. 15, that would be 5 7.5 10 12.5 15.

Each of the .. will have the same formula where I know on beforehand how much of the percentage it is, for example: 0% 25% 50% 75% 100%

What formula do I need to create this sequence? If it helps, either reference to l,h for lowest and highest in your formula, or A1 and A5 if you prefer an excel style formula.

• If $n$ is the number of missing numbers, the difference between consecutive terms must be $d=\frac{b-a}{n+1}$ , when $a$ is the first and $b$ the last number. – Peter Jul 9 '18 at 15:09
• The $k$ th element is then $a+(k-1)d$ – Peter Jul 9 '18 at 15:10

If the sequence is $l, a_1, a_2, \dots, a_n, h$ and the $a_k$ are the numbers to be determined, then there are $n+1$ gaps of equal length:

$$g : =a_1 - l = a_2 - a_1 = \dots = a_n - a_{n-1} = h - a_n.$$

It follows that $a_k = l + k\cdot g$ and

$$(n+1)\cdot g = h - l \implies g = \frac{h-l}{n+1}.$$

So the $k$-th missing number is

$$a_k = l+\frac{k(h-l)}{n+1}.$$

• Thanks. It appears that my lack of knowledge of math seems to make me fail to understand this answer... :( How would I translate this into an excel formula? – LPChip Jul 9 '18 at 15:46
• If your lowest value lies on cell A1 and your highest value on cell At, where $t$ is a number, then $n=t-2$ and cell $Ak$ will have value A1 + (k-1)*(At - A1)/(t-1). – Fimpellizieri Jul 9 '18 at 15:57
• I haven't forgotten you, but an important project came up that needed my attention. I hope to look into this tomorrow so I can see if this answered my question. – LPChip Jul 11 '18 at 21:10