Solved

Calculate difference from previous non-blank cell

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
macro for each dropdown 15 46
Consolidate xl 2010 worksheets with text 2 24
Excel - find text within text? 1 24
Formula for time spans 7 10
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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 a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

895 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

14 Experts available now in Live!

Get 1:1 Help Now