a generous guy from the site helped me a little with a formula a few days ago, but unfortunately i was unable to replicate to the rest of my original formula without getting error messages. The formula is this:
=MAP(K29:K92,L29:L92,M29:M92,LAMBDA(highs,lows,closes,
if($C$15="Long", if(and(max($K$29:highs)>($J$29*$B$4/$B$9)+$J$29,closes<max($K$29:highs)-(max($K$29:highs)-$J$29)*$F$5),max($K$29:highs),""), if(and(min($L$29:lows)<($J$29-($J$29*$B$4/$B$9)),closes>min($L$29:lows)-(min($L$29:lows)-$J$29)*$F$5),min($L$29:lows),""))))
It uses map data from 3 different columns (K, L and M)... i would like to replace all those helper/auxiliary columns with this for example:
=query(GOOGLEFINANCE("NVDA","all",today()-92,today()),"select Col3, offset 1",0)
But it wont let me: "invalid interval" :(
Later in the formula i use K$29:highs or l$29:lows to make an incremental array always starting from the first item.
Then again, i was unable to replace that K29 with the first item of the array (google finance) with the same error message: invalid interval.
The main goal here is to get rid in the final formula of those 3 columns. MAP only works with cells so i think i must use something different to avoid any auxiliary column.
Again, thank you so much for helping me.
https://docs.google.com/spreadsheets/d/1cNlUMhvnqaq0EZr88zMLluSMDvx_BYfDTJTKPybrdB4/edit?usp=sharing In P29 is the formula that i'd like to replace
You can provide the index of the current row to MAP and use INDEX/CHOOSEROWS to access the desired values.
=let(q,query(GOOGLEFINANCE("NVDA","all",today()-92,today()),"select Col3, Col4, Col5 offset 1",0),map(sequence(rows(q)), lambda(i,
let(h,CHOOSEROWS(INDEX(q,,1),SEQUENCE(i)),l,CHOOSEROWS(INDEX(q,,2),SEQUENCE(i)),c,INDEX(q,i,3),if(C15 = "Long",
if(and(max(h) > (J29 * B4 / B9) +J29, c < max(h) - (max(h) - J29) * F5),
max(h),
),
if(and(min(l) < (J29 - (J29 * B4 / B9)), c > min(l) - (min(l) - J29) * F5),
min(l),
)
)
))))