Solved

Calculate difference from previous non-blank cell

Posted on 2010-09-15
4
261 Views
Last Modified: 2013-11-05
I need a formula to calculate the difference in cells from the previous non-blank cell.  The data is formatted with dates in column A, readings (both blank and non-blank) in column B, and the difference from the previous reading in Column C.  See attached for an example.

Date            Reading	Diff
1/1/2010	54.4	0.0
1/2/2010	54.4	0.0
1/3/2010	54.4	0.0
1/4/2010	54.4	0.0
1/5/2010		
1/6/2010		
1/7/2010	54.4	0.0
1/8/2010	55.5	1.1
1/9/2010	55.5	0.0
1/10/2010	55.5	0.0
1/11/2010	56.5	1.0
1/12/2010		
1/13/2010		
1/14/2010	56.7	0.0
1/15/2010	56.7	0.0
1/16/2010	56.7	0.0
1/17/2010	56.9	0.2
1/18/2010	56.9	0.0
1/19/2010		
1/20/2010	56.9	0.0
1/21/2010	56.9	0.0
1/22/2010	56.9	0.0
1/23/2010	56.9	0.0

Open in new window

0
Comment
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
Comment Utility
I take it that the output shown in column C isn't what you want, do you want C15 to be 0.2? If so try like this:
In C2 just put a zero then in C3 this formula copied down
=IF(B3="","",B3-LOOKUP(9.9E+307,B$2:B2))
regards, barry
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
Put this in C3 and drag down:

=IF(ISERROR(B3-B2),"",B3-B2)

There's no difference for C2, since it's the start, so just set that to 0

Book1.xls
0
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 250 total points
Comment Utility
To make the formula more complete, try this:

=IF(B3="","",IF(B2="",0,IF(ISERROR(B3-B2),"",B3-B2)))

Book1.xls
0
 
LVL 2

Author Comment

by:djmurpyataagroupdotcom
Comment Utility
Thanks to both you!!  Ended up using barryhoudini's.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now