up vote 0 down vote favorite
share [g+] share [fb]

Is there any way to calculate the first and second differences of a column of numbers in Excel? My spreadsheet is not enormous but sufficiently large that typing in a formula in each row is not an appealing solution.

Does anyone have any ideas?

Edit to clarify what I mean by first and second differences...

The first differences of a column are the differences between each element and the preceding element. Second differences are the first differences of the first differences. So what I need to do is put in, say, C2, the difference between B1 and B2, C3 the difference between B2 and B3, and so on for about 40,000 rows.

link|improve this question
89% accept rate
I don't understand the question. Specifically what 'first and second differences of a column' means. Can you please clarify? – Sux2Lose Feb 2 '11 at 16:31
@Sux2Lose; I've edited the question to make clear what I'm after. – Brian Hooper Feb 2 '11 at 18:14
2  
Why can't you put the formula in C2 by hand ("=B2-B1") and then copy-and-paste that formula from C2 to C3..C40000? – coneslayer Feb 2 '11 at 18:37
@coneslayer, thank you for the suggestion. I didn't do that because I had no idea it would achieve the desired effect, rather than just creating 40000 identical formulae. – Brian Hooper Feb 3 '11 at 8:44
feedback

2 Answers

up vote 1 down vote accepted

So, you want to have =A1-B1 in C2, correct? You can select that formula in C2, copy, and then select that entire column and hit "Fill", and the forumla will replicate to every row. Or you could manually select the first place you want it, hold shift and select the last place you want it, and again use the fill feature.

link|improve this answer
@Duali, thank you. Given C2 contains =B2-B1, the copy will put =B3-B2 in C3, =B4-B3 in C4, yes? Not lots and lots of =B2-B1? I'll try it tomorrow when back at work. – Brian Hooper Feb 2 '11 at 22:48
That's correct, it will change the file names depending on where you move. Be warned though, if you move to the right it will go from =B2-B1 to =C2-C1 – Duall Feb 2 '11 at 23:06
@Duali, thank you for your help. I had to use Paste, not Fill. – Brian Hooper Feb 3 '11 at 8:31
feedback
up vote -1 down vote

I can give you the formula to get the sum of the first difference, but I'm stumped on the sum of the second difference.

First Difference, where data is in cell A1:A10

=SUM(A1:A9-A2:A10)

You must hit Ctrl+Shift+Enter when you are done typing the formula in order for it to work.

link|improve this answer
That's the same as "=A1-A10", and isn't what he's asking for. He's asking for the first differences (a list of them), not the "sum of" first differences. – coneslayer Feb 2 '11 at 19:04
Misunderstood the question. Thanks for the down vote. – Sux2Lose Feb 3 '11 at 14:10
feedback

Your Answer

 
or
required, but never shown
discard

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