Simple (?) Match question

[Guest]

I'm trying to figure out someone else's workbook. This is the formula that
produces the correct contents of the cell in data!V4 on a different
worksheet.
=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1
How do I change this formula to obtain the contents of cell data!V5 ? & V6
The E and the +307 has me most perplexed.
The A7:BA7 is number data that is entered weekly, and the V column is the
last of the data for this week, so when next week's data is entered, the
contents of cell data! W4 will be produced, and then I'd want the formula to
obtain the contents of W5 & W6.

Thanks,

Steve

 

[Guest]

I have no clue either, except the guy is a bit weird. You would think 200
zeros would be enough

 

[Guest]

9.99999999999999E+307 is scientific notation, i.e., approximately 10 *
10^307, or approximately 10 followed by 307 zeroes. Why someone is trying to
use a formula that considers such a large number, I have no clue.

Dave

 

[T. Valko]

I have no clue either, except the guy is a bit weird.

Not really, he's just "following the herd"!

The way that Lookup works is that if the lookup_value
(9.99999999999999E+307) is not found the result of the formula is the *LAST*
value in the range that is less than the lookup_value
(9.99999999999999E+307). Since it is pretty much a guarantee that every
number in the range will be less than 9.99999999999999E+307 the last number
in the range is returned.

This is something that hits a nerve with me. Suppose the numbers in your
range are golf scores. Depending on how good the players are there is
absolutely no chance that any score will be greater than 125. In this case
the lookup_value can be something like 200 rather than the pedantic
9.99999999999999E+307 (which I'm sure confuses a lot of people). How many
9's do I have to type? ?????? <argh>

Now, concerning your question. It's not clear what you're wanting to do.

=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1

That formula will return the relative position of the last number in the
range minus 1.

A7 = 1
B7 = 10
C7 = 5
D7 = 2

The above formula would return 3. 2 is the last number that is less than the
lookup_value. It's relative position is 4, minus 1 = 3.

Biff

 

[Roger Govier]

Hi Biff

I quite agree, but to ensure it is a big enough number then 99^99
usually suffices, and that is easy enough to use and remember.
Alternatively, I often define a name like bignum with Insert

Name>Define> bignum

Refers to 9.99999999999999E+307
so I don't have to think about the large number and how many 9's or what
exponent in any subsequent formulae

then use =MATCH(bignum,'Data'!A7:BA7)-1

 

[Guest]

It's not that I'm stoopid (well, maybe I am) but what I'm trying to do is
return the reults of the follwowing cells:
this formula
=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1
correctly returns the result of cell w4 on the data sheet.
I also need the results of w5 and w6.
The last entered data is in cell w7, which is in the A7:BA7 range. I can't
figure out how the formula is getting the W4 cell, and all I need is the W5
and W6 cell also.

Much thanks,

Steve

 

[T. Valko]

this formula

=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1
correctly returns the result of cell w4 on the data sheet.

Sorry, but I'm not following you on this.

The result of the formula may be the same as the value in cell W4 but the
formula has nothing at all to do with cell W4. How does the range
'Data'!A7:BA7 relate to cell W4?

Biff

 

[Guest]

That's what I can't figure out.
I have weeks 1 thru 52 in in cells B4:AB4
When I enter data in W7, the formula'ed result is 22 ( which is cell W4);
when I enter data in X7 for the next week, the formula'ed result is 23 (which
is in cell X4), etc. This part is working as needed.
What I was trying also to get is what is in W5, W6, etc., which is the 1st
day of each week, but surely can't understand how that formula is getting the
result from W4.
The only way 'Data'!A7:BA7 relates to cell W4 is that when the currrent
weeks data is entered in that range (w7), the corresponding week # (22) is
shown, which is in cell W4. When the next weeks data is entered in X7, week
23 (X4) is shown.

 

[T. Valko]

can't understand how that formula is getting the

result from W4

That's where you're getting confused. The formula:

=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1

*IS NOT* getting the result from cell W4. The formula is getting the result
from 'Data'!A7:BA7.

I still don't understand what you're trying to do. If you want to send a
copy of the file to me so I can see for myself I'll have a better idea of
you want. If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Guest]

Just sent.

Thanks,

That's where you're getting confused. The formula:

=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1

*IS NOT* getting the result from cell W4. The formula is getting the result
from 'Data'!A7:BA7.

I still don't understand what you're trying to do. If you want to send a
copy of the file to me so I can see for myself I'll have a better idea of
you want. If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[Guest]

Biff,

Your fix worked great. Thanks,

Steve

Just sent.

Thanks,

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Biff,

Your fix worked great. Thanks,

Steve