3

I'm trying to do some work in Excel and if I found a formula for this sequence it would help a lot. I don't particularly need to know why the formula works.

I have found the sequence here .

But there in no nice form for it.

Thanks!

learner
  • 6,726
Alexv
  • 31

2 Answers2

7

The first expression given under FORMULA is quite straightforward:

$$a_n=n-\frac{m(m+1)}2+1,\quad\text{where}\quad m=\left\lfloor\frac{\sqrt{8n+1}}2\right\rfloor\;.$$

Everything here can be done in Excel.

Brian M. Scott
  • 616,228
  • Wow thanks a lot. Just out of curiosity is there a somewhat simple way to work these sort of things out? – Alexv May 24 '13 at 06:37
  • @Alexander: You’re welcome. I’m not sure what you’d classify as simple; you can write a single spreadsheet formula that calculates $a_n$ as a function of $n$ using this formula, so I tend to think of it as being at least reasonably simple. – Brian M. Scott May 24 '13 at 06:43
1

I've pulled this out of a comment, though it doesn't quite answer the question. There are some flexible ways of generating sequences in Excel which don't depend on a closed formula. If you need to reference a sequence within a formula, then you can generate the sequence (eg on another worksheet) and use a "lookup" formula to pick the term you need. There are ways of doing this (using a name for the cells containing the sequence) which can make the final formula easier to read and check.

For example, to generate the sequence you have asked about in this question, put 1 in cells A1 and B1, and use the formula =IF(B1>MAX($A1:A1),1,B1+1) in cell C1. Then copy this formula across the first row. I've left the formula in plain text, so you ought to be able to copy it. Obvious adjustments would apply for this to work in a different row, or in a column.

There are limitations on this technique, of course, but it generates shorter formulae, which can be easier to understand when you come back to them later.

Mark Bennet
  • 100,194