In Excel 2000, my worksheet, column A is consecutive dates and column B is meter readings, that increase daily. Since the meters aren't read daily, there are some blank cells in column B (and I need to keep the blanks). I need to be able to subtract the previous meter reading from the current reading and have the results appear in column C. The blank cells in column B keep me from writing a formula for column C to do the calculation. Is there a way to write the formula to have excel skip the blank cells and do the subtraction from the next (previous) cell that contains data (i.e. meter reading)??? I sure would appreciate some help on this!!!

Create this macro: Sub reading() ' ' reading Macro ' Macro recorded 6/29/2003 by David Chase ' ' Dim offset As Integer offset = -1 Line1: If ActiveCell.offset(offset, -1).Value = 0 Then offset = offset - 1 GoTo Line1 Else ActiveCell.FormulaR1C1 = "=RC[-1]-R[" & offset & "]C[-1]" End If End Sub

you could also use this formula in column C =IF(OR(B1="",BE2=""),"",(B2-B1)) it says if cells B1 or B2 are blank, insert blank in the current cell otherwise subtract the contents of B1 from the contents of B2 and insert the result in the current cell. this formula would begin at cell C2. You can select it, place the cursor over the lower right hand corner, left click and drag it down column C to auto fill the rest with the appropriate cell references. Any cell references you wish to remain constant can be augmented with the "$" symbol like this: =B4 becomes =$B$4. if you wisy to only partially lock it you can also do this =$B4 which locks only the column reference and not the row reference.

Rayman's answer will work, but only if there is just one blank, two or three blanks would throw it off. I should also add that, if you go the macro route, you click on the cell where you want the result, then run the macro.

You state that the Blank cells are important to remain. Is it or would it be Ok to have the blank cell filled with the previous reading when there is a blank? If so then go to Edit, Go To, Special, Blanks. Hit = and the Up Arrow key and then hit Ctrl+Enter key. This will fill all blank cells with the value of the cell above it. Then in C2 you can use =B2-B1 (assuming your values are in Column B) and drag it down. HTH Bryan

If the Blank cells are important to remain as visually blank then after going to Edit, Go To, Blanks then go to the Font color button and select White as the font color for the blank cells. Then = and the Up arrow key, Ctrl + Enter. Then the blank cells will be filled with the values of the cell above it but you will not be able to see the values. The cells will appear to still be as Blank. HTH2 Bryan

Thank you so much to all respondents!!! Special thanks to Bryan who's suggestions were perfect for my needs! Thanks also to Chase and Rayman...I don't have much experience with either macros or IF functions, but will also try your recommendations to further my knowledge base. Thanks Again! :)