Google-sheets – Calculating annual return of stocks with Google finance data

I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;
}

function myRandom() {
  # Some function
  return Math.random();
}

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;
}

I would enter

=array_constrain(filter(C$2:C$5, (A$2:A$5 <= A8) * (B$2:B$5 >= A8)), 1, 1)

in cell B8, and copy it down the results array. Explanation:

• filter keeps only the cells from C where A8 falls between A and B.
• array_constrain makes sure at most one result is returned (this may be unnecessary if you are sure the ranges in the lookup array do not overlap).