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

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


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think 200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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
NameDefine 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

--
Regards

Roger Govier


"T. Valko" wrote in message
...
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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think 200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

Just sent.

Thanks,


"T. Valko" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

Biff,

Your fix worked great. Thanks,

Steve

"Steve" wrote:

Just sent.

Thanks,


"T. Valko" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Steve" wrote:

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

You're welcome. Thanks for the feedback!

Biff

"Steve" wrote in message
...
Biff,

Your fix worked great. Thanks,

Steve

"Steve" wrote:

Just sent.

Thanks,


"T. Valko" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
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" wrote:

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

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would
think
200
zeros would be enough :)


"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more
likely to
be
answered than questions that provide no detail about your
problem.


"Steve" wrote:

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