Solved

Calculate difference from previous non-blank cell

Posted on 2010-09-15
4
265 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 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