0

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...

enter image description here

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")

mkrieger1
  • 19,194
  • 5
  • 54
  • 65
boten500
  • 123
  • 1
  • 1
  • 8

1 Answers1

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • thanks for the reply! I tried using it in arrays like you said but then I got another error "there were in the ARRAY_LITERAL function 1 or more missing values" you know how I can go around it? – boten500 Oct 01 '22 at 15:01
  • 1
    @boten500 did you check 2nd link? – player0 Oct 01 '22 at 15:02
  • So yea I didn't read it before thanks tho, now it's working apparently the problem was that one formula in the array didn't have the corresponding data in the extension I am pulling the data from so I pulled N/A error, but I'd like to use your array generator from the first link but the thing is I didn't quite understand how to use it in my case, it'd be great if you can explain (I didn't know most of the functions used in the formula there so I ended up researching most of them on the way also sorry if I am asking a lot of things I really appreciate your help) – boten500 Oct 01 '22 at 17:36
  • @boten500 see: https://docs.google.com/spreadsheets/d/1AS5inCCX-TygokNZog2dzqXiPlel3281DBRNUg_1UTE/edit#gid=1420679603 – player0 Oct 01 '22 at 17:55
  • sorry if I am being ignorant but I just don't understand what's going on in the sheet, from what you said before that it is a formula generator I suppose you want it to be able to take my own formula in the array and add more of that formula in the cell with ascending order starting from the last formula's row number, did I get it right? if you can picture that sheet and explain me the mechanics I think I might understand it better. – boten500 Oct 01 '22 at 18:58
  • @boten500 see the sheet I added some notes. make a copy of it and try to play with it by changing values in Sheet2!B1 and Sheet2!B2 – player0 Oct 01 '22 at 19:52