Need help with a formula

  • Thread starter Thread starter BerrSD
  • Start date Start date
B

BerrSD

I have this formula: =INDEX(L11:L131,MATCH(99999,L11:L131))
It was working and now it doesn't. Cannot figure out why. I need help.
It's supposed to work like this:
There is an array, for example B1:B4 and a cell A1

Initially;

B1 = Null A1 = Null
B2 = Null
B3 = Null
B4 = Null

First Pass

B1 = 20 A1 = 20
B2 = Null
B3 = Null
B4 = Null

Second Pass

B1 = 20 A1 = 4
B2 = 4
B3 = Null
B4 = Null

Third Pass

B1 = 20 A1 = 31
B2 = 4
B3 = 31
B4 = Null

Fourth Pass

B1 = 20 A1 = 66
B2 = 4
B3 = 31
B4 = 66


My problem is that A1 displays 0 and it shouldn't. There is something
missing in the formula but I don't know what. I reposted this before but my
former post is no longer here. Should've printed it out.
Any help would be greatly appreciated. Thanks.

Bernard
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
If desired, send your file to my address below. I will only look
if: 1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
Click to expand...

I cannot send the file. It's confidential and proprietary. You gave me the
forula and as I said, it worked and then stopped. I tried finding my former
post so that I could go over it again but it's no longer here. What
happened to it?

Bernard
 
I never gave you anything and don't care about your data. Surely you can
provide "DUMMY" data. Perhaps it is as simple as you are using a formula for
column L when you want another column. If so, you must have attended Texas A
& M.
 
=INDEX(B1:B4,MATCH(99999,B1:B4))
Click to expand...

INDEX(L11:L131,MATCH(99999,L11:L131))

Same formula except one works the other doesn't. Got the second formula to
work on a new worksheet but not on my sheet.


Bernard
 
I never gave you anything and don't care about your data. Surely you
can provide "DUMMY" data. Perhaps it is as simple as you are using a
formula for column L when you want another column. If so, you must
have attended Texas A & M.
Click to expand...

That'a a rude thing to write! :-( I'm asking for help and I'm getting
wisecracks. I figured out the problem. My array L11:L131 contains the
results of a computation.The cells were originally initialized to 0. They
should have been initialized to "Null." If they're 0, then the last cell is
0, wherefor, my result cell is 0. When I initialized them to "Null" it
worked great.

BTW, I did print out my first post...and I did get the formula from you.
Take care.

Bernard
 
Why aren't you using?

=INDEX(B1:B4,MATCH(99999,B1:B4))
Click to expand...

I figured out the problem. My array L11:L131 contains the results of a
computation.The cells were originally initialized to 0. They should have
been initialized to "Null." If they're 0, then the last cell is 0,
wherefor, my result cell is 0. When I initialized them to "Null" it worked
great.

Thanks for your help.

Bernard
 
Back
Top