Solved

Calculate difference from previous non-blank cell

Posted on 2010-09-15
4
263 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
ID: 33683792
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
ID: 33683800
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
ID: 33683853
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
ID: 33702831
Thanks to both you!!  Ended up using barryhoudini's.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

770 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