Index, Lookup and Vectors/Arrays

M

Mike

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!
 
R

Roger Govier

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
 
M

Mike

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?
 
R

RagDyeR

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

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?
 
R

Roger Govier

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))
 
M

Mike

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!
 
M

Mike

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

Roger Govier

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!!!
 
Top