calculation problem due to ongoing field

I do calculate in a fix field the yield of a data-set as follow:

=Kurs!B593/Kurs!B2-1

which works perfect. But I have to change every day the field-link which I would like to avoid and tried therefore

=Kurs!B2000/Kurs!B2-1

which doesn't work as B2000 is not yet filled with a value. I tried this because on diagram, this works fine. Any idea what's the best way to get yield without manualy changing the formula?
thx
Kongta
KongtaAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
Here's one better :)

=LOOKUP(9.99999999999999E+307,B:B)/B1-1

or
=Lookup(99^99,B:B)/B1-1

See attached

Dave
test-r2.xlsx
0
 
roger_karamCommented:
Hello Kongta, do you have a sample sheet? It would be easier to understand what you are looking for.
-RK
0
 
KongtaAuthor Commented:
0
 
dlmilleCommented:
You can use the formula
=INDIRECT(ADDRESS(MATCH(99^99,$B1:$B$65536),2))/B1-1


See attached

Dave
test-r1.xlsx
0
 
KongtaAuthor Commented:
bingo, cool, have many thx
0
All Courses

From novice to tech pro — start learning today.