How can I drag down to multiple rows a formula which has more than 1 row in its result?-CodePudding

I have an extension I am getting the data from, and I am referring to that extension in a formula with result of pre determined rows but I want more than 1 row in its result and that's when I have this problem, because I want to drag the formula to multiple rows but they overlap each other, for example if I wanted 3 rows in the result of the formula starting in row 1 and then drag it down from row 1 to row 3 the formula in row 1 and 2 will show an error because they're overlapped in each other I will put a picture in how it looks...

Is there a way to specify amount of rows as a space between each formula in a way that when I drag the formula down to more rows it will adjust to the "space" I specified?

This is the formula I am using, I am also referring to another sheet as you can see so it'll be great if you can use this formula to answer my question, if I can specify the "space" using another formula that is (also it's probably obvious but the pre determined rows in the formula is the "2d").

=CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d")

CodePudding user response：

this is usually solved by constructing an array of formulae where you stack them up in the line like:

={CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C4&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C5&"/USD", "price_history", "2d");
  CRYPTOFINANCE("KRAKEN:"&'crypto-track'!C6&"/USD", "price_history", "2d")}

this way the 2nd fx will pick up right after 1 fx ends

you can ease your pain of a "hand job" from constructing such an array - especially if that array needs to span over the larger range - by building a formula to generate a formula. for example: https://stackoverflow.com/a/68278101/5632629

also, make sure you obey the law of array constructs and successfully avoid all array errors - https://stackoverflow.com/a/58042211/5632629