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.

  • 1
    $\begingroup$ 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. $\endgroup$ – Peter Jul 9 '18 at 15:09
  • $\begingroup$ The $k$ th element is then $a+(k-1)d$ $\endgroup$ – 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}.$$

  • $\begingroup$ 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? $\endgroup$ – LPChip Jul 9 '18 at 15:46
  • $\begingroup$ 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). $\endgroup$ – Fimpellizieri Jul 9 '18 at 15:57
  • $\begingroup$ 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. $\endgroup$ – LPChip Jul 11 '18 at 21:10

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.