I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I fill
in a cell each week starting with D1, and after 9 weeks I fill in D9. I need
to create a formula that will find the furthest cell to the right to make my
solution accurate.

Basically, I have a constant number witch is integrated into the formula,
but my variable will change... and hopefully automatically based on it being
the furthest cell to the right or whatever the technical way of saying it
would be.

Thanks for your help!

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I
fill in a cell each week starting with D1, and after 9 weeks I fill
in D9. I need to create a formula that will find the furthest cell
to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

Hi, sorry, but that doesn't make sense to me. I am going to enter in
different values in d1 through d9, but the formula where I divide 88/(the
furthest number to the right out of d1-d9). If I put in a match formula in
d1-9, then I won't be able to have a value... right?

"Roger Govier" wrote:

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I
fill in a cell each week starting with D1, and after 9 weeks I fill
in D9. I need to create a formula that will find the furthest cell
to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

You already know that the furthest number is 88?

Is it the numerator or the denominator?

How about a more explicit explanation, with examples of data and exactly
what you're looking to accomplish?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Mike" wrote in message
...
Hi, sorry, but that doesn't make sense to me. I am going to enter in
different values in d1 through d9, but the formula where I divide 88/(the
furthest number to the right out of d1-d9). If I put in a match formula in
d1-9, then I won't be able to have a value... right?

"Roger Govier" wrote:

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I
fill in a cell each week starting with D1, and after 9 weeks I fill
in D9. I need to create a formula that will find the furthest cell
to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

Hi Mike

You are wanting the value in the cell furthest right from D1, not the
cell location, so change the formula to
=LOOKUP(9.99999999999999E+307,1:1)

e.g. =(88/LOOKUP(9.99999999999999E+307,1:1))

Obviously, which cell you put the formula in is up to you (I agree, not
in D,1 although my wording was totally unclear, I mean't this would give
the value relative to D1).

If you are wanting the formula to go on the same row, and your data does
not extend beyond say column N, then in O1
=(88/LOOKUP(9.99999999999999E+307,D1:N1))

--
Regards

Roger Govier



Mike wrote:
Hi, sorry, but that doesn't make sense to me. I am going to enter in
different values in d1 through d9, but the formula where I divide
88/(the furthest number to the right out of d1-d9). If I put in a
match formula in d1-9, then I won't be able to have a value... right?

"Roger Govier" wrote:

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks.
I fill in a cell each week starting with D1, and after 9 weeks I
fill in D9. I need to create a formula that will find the furthest
cell to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

yes, sorry. I should have filled in the spaces a little bit. I am making a
spreadsheet for an office competition. We are having a weight loss
competition over the next 3 months with weekly weigh-ins every monday.
Basically, we're competing based on percentage of weight lost, and every week
I want the percentage to be updated based on the new weight. So, I have 13
weeks where I'm going to be inputing the new numbers for each person. I
don't want to go in and change the formula each week to be up to date with
the new weight, so I was hoping that there was a way for excel to take the
value in the furthest right cell in a row.

The formula will be something like this (100%-(Cell furthest to the right
between g4:s4)/(original weight)). That will give me the percentage of
weight lost...

I hope this makes a little more sense... and thank you so much for your help!



"RagDyeR" wrote:

You already know that the furthest number is 88?

Is it the numerator or the denominator?

How about a more explicit explanation, with examples of data and exactly
what you're looking to accomplish?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Mike" wrote in message
...
Hi, sorry, but that doesn't make sense to me. I am going to enter in
different values in d1 through d9, but the formula where I divide 88/(the
furthest number to the right out of d1-d9). If I put in a match formula in
d1-9, then I won't be able to have a value... right?

"Roger Govier" wrote:

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks. I
fill in a cell each week starting with D1, and after 9 weeks I fill
in D9. I need to create a formula that will find the furthest cell
to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

Awesome, that worked! Sorry for all the confusion. I'm currious though,
would you mind explaining to me the 9.99999 part?

"Roger Govier" wrote:

Hi Mike

You are wanting the value in the cell furthest right from D1, not the
cell location, so change the formula to
=LOOKUP(9.99999999999999E+307,1:1)

e.g. =(88/LOOKUP(9.99999999999999E+307,1:1))

Obviously, which cell you put the formula in is up to you (I agree, not
in D,1 although my wording was totally unclear, I mean't this would give
the value relative to D1).

If you are wanting the formula to go on the same row, and your data does
not extend beyond say column N, then in O1
=(88/LOOKUP(9.99999999999999E+307,D1:N1))

--
Regards

Roger Govier



Mike wrote:
Hi, sorry, but that doesn't make sense to me. I am going to enter in
different values in d1 through d9, but the formula where I divide
88/(the furthest number to the right out of d1-d9). If I put in a
match formula in d1-9, then I won't be able to have a value... right?

"Roger Govier" wrote:

Hi Mike

If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9

--
Regards

Roger Govier



Mike wrote:
I am looking to find out if there is a way to do this:

I have a vector of values say (D1:D9) witch equal different weeks.
I fill in a cell each week starting with D1, and after 9 weeks I
fill in D9. I need to create a formula that will find the furthest
cell to the right to make my solution accurate.

Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully automatically
based on it being the furthest cell to the right or whatever the
technical way of saying it would be.

Thanks for your help!

wrote
right?
"Roger Govier" wrote:
Hi Mike
If the data is numeric, then for D1
=MATCH(9.99999999999999E+307,1:1)
if it is text then
=MATCH(REPT("Z",255),1:1)
Copy down and it will alter for rows 2:9
--
Regards
Roger Govier
Mike wrote:
I am looking to find out if there is a way to do this:
I have a vector of values say (D1:D9) witch equal different weeks.
I fill in a cell each week starting with D1, and after 9 weeks I
fill in D9. I need to create a formula that will find the
furthest cell to the right to make my solution accurate.
Basically, I have a constant number witch is integrated into the
formula, but my variable will change... and hopefully
automatically based on it being the furthest cell to the right or
whatever the technical way of saying it would be.
Thanks for your help!

Hi Mike

Glad it worked out for you.
Basically, that is the largest number that could be found in Excel, and
because it dosen't exist in the range being searched, Lookup is giving
the last value found that was not equal to that number.
For the range of values you are considering, then 999 would have been
plenty high enough!!!


--
Regards

Roger Govier



Mike wrote:
Awesome, that worked! Sorry for all the confusion. I'm currious
though, would you mind explaining to me the 9.99999 part?

"Roger Govier" wrote:

Hi Mike

You are wanting the value in the cell furthest right from D1, not the
cell location, so change the formula to
=LOOKUP(9.99999999999999E+307,1:1)

e.g. =(88/LOOKUP(9.99999999999999E+307,1:1))

Obviously, which cell you put the formula in is up to you (I agree,
not in D,1 although my wording was totally unclear, I mean't this
would give the value relative to D1).

If you are wanting the formula to go on the same row, and your data
does not extend beyond say column N, then in O1
=(88/LOOKUP(9.99999999999999E+307,D1:N1))

--
Regards

Roger Govier



Mike wrote:
Hi, sorry, but that doesn't make sense to me. I am going to enter
in different values in d1 through d9, but the formula where I divide
88/(the furthest number to the right out of d1-d9). If I put in a