Subtraction formula for consecutive cells in a column, skipping blanks

Column with Formulas (results of differences)
6 Blank
8 2
4 -4
Blank Blank
2 -2
19 17
2 -17
Blank Blank
Blank Blank
4 2

I want a separate column (the right column) that has a standard formula
that can go along side the left column that represents the differences
between consecutive cells in this left vertical column. It should have
the results noted on the right column. Do you know what formula that I
could use as a standard, filled down through the second column that
would get these variance results?

Thank you,

Dave

Hi!

Try this:

Assume your data is in the range A1:An

Enter this formula in cell B2 and copy down as needed:

=IF(OR(A2="",COUNT(A$1:A2)<2),"",A2-LOOKUP(9.99999999999999E+307,A$1:A1))

If the first cell in the data list will always have an entry then you can
use this:

=IF(A2="","",A2-LOOKUP(9.99999999999999E+307,A$1:A1))

Biff

<[email protected]> wrote in message
news:[email protected]...
> Column with Formulas (results of differences)
> 6 Blank
> 8 2
> 4 -4
> Blank Blank
> 2 -2
> 19 17
> 2 -17
> Blank Blank
> Blank Blank
> 4 2
>
> I want a separate column (the right column) that has a standard formula
> that can go along side the left column that represents the differences
> between consecutive cells in this left vertical column. It should have
> the results noted on the right column. Do you know what formula that I
> could use as a standard, filled down through the second column that
> would get these variance results?
>
> Thank you,
>
> Dave
>

Biff Rocks!!!

An amazing formula Biff. It really works. I have it subtracting weekly
data and skipping any days, like Sundays, with zero data. Excellent.
One column it was refering to was getting formulas from another
worksheet, so when I used your formula, it calculated correctly, but
caused an #VALUE! error for days that should be blank. THEN, I got the
bright idea of modifying the values in your formula to point to the
other worksheet just like the column it was refering to... Once I did
this, no more #VALUE! errors. Beautiful.

I really appreciate your quick and brilliant assistance with this
matter.

Cheers!

Dave


Biff wrote:
> Hi!
>
> Try this:
>
> Assume your data is in the range A1:An
>
> Enter this formula in cell B2 and copy down as needed:
>
> =IF(OR(A2="",COUNT(A$1:A2)<2),"",A2-LOOKUP(9.99999999999999E+307,A$1:A1))
>
> If the first cell in the data list will always have an entry then you can
> use this:
>
> =IF(A2="","",A2-LOOKUP(9.99999999999999E+307,A$1:A1))
>
> Biff
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Column with Formulas (results of differences)
> > 6 Blank
> > 8 2
> > 4 -4
> > Blank Blank
> > 2 -2
> > 19 17
> > 2 -17
> > Blank Blank
> > Blank Blank
> > 4 2
> >
> > I want a separate column (the right column) that has a standard formula
> > that can go along side the left column that represents the differences
> > between consecutive cells in this left vertical column. It should have
> > the results noted on the right column. Do you know what formula that I
> > could use as a standard, filled down through the second column that
> > would get these variance results?
> >
> > Thank you,
> >
> > Dave
> >