Lets say I have 100 rows in column A. That would be 100 cells with a value in each. I only want the value of the difference between every 5 cells. For example, in the first cell, A5-A1, then next cell down A10-A5, A15-A10, A20-A15, etc. I want to be able to copy a formula down that would pull those values from down the sheet.

The reason is I have a value every second of time, I want a rate every minute. Every second is too much info, too noisy.

  • 1. Your first range (A5-A1) is shorter than your others. 2. Still, what you are describing could be accomplished with a pair of offset() functions (along the lines of b2 =offset($a$1, row()*5,0)-offset($a$1, (row()-1)*5,0) 3. Consider making a second column that has a running average. (e.g. b3 = average(a1:a5) ) This should smooth out the noise a bit. – Adam Jul 29 '15 at 16:39
  • Thanks Adam, I'm still having a hard time, I was giving a scenario, let me give exact cells and if you would do the formula. B2=D40-D2, B3=D80-D42, B4=D120-D82. You see the pattern, I move down 40 cells each time. Thanks Adam for helping me. – Judburns Jul 29 '15 at 17:13

What you are describing could be accomplished with a pair of offset() functions:

B2 =offset($a$1, row()*5,0)-offset($a$1, (row()-1)*5,0)

offset expects a starting cell, the number of rows to offset by and the number of columns to offset by.

row() returns the current row number

so =offset($a$1, row()*5,0) references A5 in the first row (row()*5 = 1*5 =5)

If you put the same formula anywhere in row 2, it will reference A10 (row()*5 = 2*5 =10)

In your follow on question, you want B2=D40-D2, B3=D80-D42, B4=D120-D82

so B2 =offset($d$2, (row()-1)*40,0) -offset($d$2, (row()-2)*40+2,0)

Fill down.

  • But ....why don't you just calculate a moving average? Why are you skipping over D41, D81, D121, etc? – Adam Jul 29 '15 at 17:58
  • Thank you very much for your help Adam. Everything works great now. As far as the moving average, it's something for us to think about, but since we're working with time units, instead of a temperature data point every second, which goes up and down slightly, a data point every minute will give a better slope. Thanks again Adam, you really know your excel. – Judburns Jul 30 '15 at 10:29
  • Glad it helps - I hope the explanation of how it works made some sense too. – Adam Jul 30 '15 at 14:51

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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