Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

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
0
Kongta
Asked:
Kongta
  • 2
  • 2
1 Solution
 
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
 
dlmilleCommented:
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
 
KongtaAuthor Commented:
bingo, cool, have many thx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now