Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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

wrote in message
ups.com...
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



  #3   Report Post  
Keyz
 
Posts: n/a
Default

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

wrote in message
ups.com...
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
see the cells the formula relates to by double clicking the cell pvagolfer Excel Discussion (Misc queries) 1 May 16th 05 12:25 AM
trying to create an (almost) circular formula between cells and data validated cells with lists KR Excel Worksheet Functions 0 May 12th 05 07:21 PM
Macro or Formula to remove Text from Cells smck Excel Worksheet Functions 6 May 11th 05 03:22 AM
How do i select different cells to be use in a formula. Manuel Excel Discussion (Misc queries) 1 January 13th 05 04:52 PM
How do I set up a formula to repat 5 consecutive cells in a colum. jbsand1001 Excel Worksheet Functions 2 January 7th 05 09:36 PM


All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"