Sequences in Excel - The Nth Term

[Futile Crush]

Hey guys. I'm new to the Mr Excel message board, I just came out because I had a question that interested me.

Sequences. Those lovely little chains going up by a certain number, like 1, 2, 3, 4 ... or 2, 4, 6, 8 ... or 5, 3, 1, -1 ... they have a common term that dictates the sequence. For example, a simple sequence like 1, 2, 3, 4, is N, or the number. A sequence like 2, 3, 4, 5 is N+1, because, say that N was 1, 1+1 is 2, which is the first number in the sequence, then the 2nd number in the sequence is 2+1, which is 3.

Then, there're sequences like N squared, 1, 4, 9, 16 and so on. 1 squared is 1, 2 squared is 4, 3 squared is 9, 4 squared is 16, etcetera. The difference is calculated by working out the difference between the numbers, then working out the difference between the differences, for example, 1+3=4, 4+5=9, 5-3=2. Then, that 2 is halved and put on the front of N2 (n squared), so it's 1N2, or N2.

Anyway, that's the basic maths behind it, for the people like Excelisfun / Mike who claim to have fallen out of it down to all his Excelling, and here's the question.

Can this be done in Excel?

If I were to set up a simple table with 1, 2, 3, 4 ... up to 10 going along the columns, is there anyway to set up a formula in each of the cells below to specify an Nth term and generate a sequence. For instance, if there was some way to input "N squared" or POWER(*cell*,2), but also have that cell set up so that virtually any Nth term could be inputted from another cell or other sheet and it would generate the sequence. Would I need to use multiple cells with drop-down lists? Should I be using nested IFs or set up another cell where a code number can be put in and the IF will put in a certain Nth term based on values then entered? I dunno, I was hoping somebody could help me with this.

If anyone can offer any insights, that'd be greatly appreciated. Thanks

 

[Futile Crush]

In fact, never mind . I've actually gone and figured it out myself. Dunno how to edit the first post out, so I'll just post here and say you don't need to offer any help

 

[WhoCanDo]

If your not working on anything confidential then it would be nice to see your answer for future reference please.

 

[Futile Crush]

If your not working on anything confidential ...

Yeah, I'm working on some top secret mathematical formula for my spy company, they're all about the Nth term .

Yeah, I know why I couldn't do it at first, because I was trying to be be overly clever and do it and one formula and in one cell so I could type in "N2" or "N+1" or "5N" or "N2-7" etcetera. I did a table, with 1 to 10 along the top, and three rows, one for straightforward DMAS Nth terms (division, multiplication, addition and subtraction,) a second for powered Nth terms such as N squared, N cubed, N to the power of 5 and so on, and a third for both, such as N squared minus 1.

I put "N", just for the sake of bureaucracy, then next to it put a box with a spin button for the purposes of ease, that would be the box for Power. Then, on the one below I put N again, and a drop-down list with +, -, * and /. After that I put the number to add, multiply, subtract or divide. In the table with 1 to 10, I put a multiple IF formula that basically says "If it's plus, then add that number to the number on the table, if it's minus ..." etcetera. I can't show you the formula, I haven't got Excel up, but it was simple enough, although a little tedious. Below that, I put both a box for a Power and a box for adding, subtracting etc. something to it. At the end of all these I concatenated the numbers to generate an Nth term that looks something like "7N+2" or "N2". I then put those next to the table along the appropriate rows.

It works . I just input a couple of numbers and I've got an Nth term.

Sorry if that explanation was a little tedious. Added to the fact that this thread was pretty pointless when I figured out the answer anyway, lol.

 

[Futile Crush]

Here's an annotated screenshot. Hope it's clear enough to make out the formulas.