ExcelBanter - View Single Post

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