Compare two values if there are gaps in the data series

  • Thread starter Thread starter David Fodden
  • Start date Start date
D

David Fodden

Hello everyone

I'm using Excel 97 and posted a problem on another NG recently which
resulted in some solutions but none worked as I had hoped so I thought that
I'd post again but coming from a different angle to see if anyone else had
any ideas that I could "tweak".

I have a row of numerical data from A1 to as far as necessary. There may be
gaps in the data where cells are left blank. I need to be able to determine
the difference between the final entry in the row and that entered in the
cell immediately to it's left (this value is to be entered into G4). If
that cell is blank, I need to go towards the left until I come across a cell
that contains a number. There is no need to determine if the data is
numerical or text because it's always numerical (or blank). Is there a
worksheet function (or combination) that I would put into G4 to allow me to
do that? I've thought of having a dynamic named range that extends as new
data is entered and think that OFFSET may be needed but I don't know if this
is possible.

If it can't be done using worksheet function(s), could it be done easily
with VBA? If so, I'd need a push in the right direction! It would need to
fire when the new data is entered into the row of data.

Thanks for your time.

David
 
Hello David

I haven't seen the other solutions, so it's hard to tell, if my suggestion
will work. Anyway, from my understanding of the problem,
here's one way to do it:

In G4 enter this arrayformula in one line:

=INDEX(1:1,MAX((1:1<>"")*COLUMN(1:1)))-
INDEX(1:1,LARGE((1:1<>"")*COLUMN(1:1),2))

The formula must be entered with <Shift><Ctrl><Enter> instead of <Enter>
also if edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { }. Don't enter these brackets
yourself. They are Excel's way of showing, that the formula is calculated as
an arrayformula.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.
 
I haven't seen the other solutions, so it's hard to tell, if my suggestion
will work. Anyway, from my understanding of the problem,
here's one way to do it:

In G4 enter this arrayformula in one line:

=INDEX(1:1,MAX((1:1<>"")*COLUMN(1:1)))-
INDEX(1:1,LARGE((1:1<>"")*COLUMN(1:1),2))
Click to expand...
...

Variation on the same theme, but the following doesn't have to be array-entered.

=SUMPRODUCT({1,-1},N(OFFSET(1:1,0,LARGE((1:1<>"")*(COLUMN(1:1)-1),{1,2}),1,1)))
 
Thank you Leo and Harlan for the solutions - they seem to work fine as
described but, as I indicated in the original post, I would have to "tweak"
the solution for my actual problem.

What I need to do is have the formula as one of the criteria in Conditional
Formatting. The formatting that is applied to the "target" cell, responds
to the difference between it and the cell immediately to its left which has
a valid numerical value. Leo's solution works in part, but the whole row of
cells changes colour when any of the cells in it meets the conditional
format (I need only the cell that has the difference greater than that
specified in the CF to change). I tried to enter Harlan's solution as a
formula into the CF and Excel told me that Array Constants (and others)
couldn't be used in CF!

I know that I'm so close to the solution and hope that one of these can be
tweaked to do what I need.

Any more suggestions???

Thanks for your time and patience.

David

Harlan Grove said:
...
..

Variation on the same theme, but the following doesn't have to be array-entered.
Click to expand...
=SUMPRODUCT({1 said:
--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something
Click to expand...
else.
 
You're welcome, David.

I believe this setup will do the job:

1. Select the entire row 1 (by clicking on the row-number)
2. Choose Formats > Conditional formatting (or similar)
3. Insert this formula in one line and enter with <Enter>
(CF works on array formulae, but just enter them with <Enter>)

=(A1<>"")*(INDEX($A$1:A1,MAX(($A$1:A1<>"")*COLUMN($A$1:A1)))-
INDEX($A$1:A1,LARGE(($A$1:A1<>"")*COLUMN($A$1:A1),2)))>6

Please notice the use of mixed absolute ($$) and relative (no cash)
references.

Alter for the actual difference (here >6)

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only, please.
 
Brilliant Leo - it works just fine! I would never have come up with that
myself.

Having read the various NGs for some time, I have been in the fortunate
situation of being able to answer a VERY few question posed by others. I
know exactly what satisfaction that brings but MVPs such as you must wallow
in such pleasure!

David
 
Thanks for the nice words, David!

You´re right. It brings great satisfaction to help
others, especially when those others, like you,
show their appreciation of the help.
Unfortunately far too many users of these groups
wouldn't dream of posting a simple "Thanks for the help."
Go´ figure!
 
Back
Top