How should a name be assigned to a range within the Let() function

English
#1

Within a Let() function, I want to assign a name to an array or range. I have tried defining the array using the Offset() function. But, I have found that Let() assigns a name to no more than a single cell when using the OFFSET() function. On the other hand, if I write out the range (eg. A1:A4), a name is assigned to the full range.

Example. Assume cells A1:A4 are filled with numbers (eg 1; 2 ;3 ; 4).
=COUNT(OFFSET(A1,0,0,4,1)) returns 4
=SUM(OFFSET(A1,0,0,4,1) returns 10
=LET(aName, A1:A4, COUNT(aName)) returns 4.
=LET(aName, A1:A4, SUM(aName)) returns 10.
=LET(aName, OFFSET(A1,0,0,4,1), COUNT(aName)) returns 1
=LET(aName, OFFSET(A1,0,0,4,1), SUM(aName)) returns 3 when the formula is located in row 3 or 4 when the formula is located in row 4.

How can I assign a name to a range in the LET() function without manually typing in the range each time?

LO 8.4.2 running on Windows 10

#2

Maybe you missed the difference between:
relativ_versus_absolute_References.ods (10.3 KB)

Extra-hint LET is for complex cases, not for triviality, maybe you should work with Named-Ranges instead?!
eg. select A1:A4 ⇒ type aname into the Addressfield above Column A and hit enter!

1 Like
#3

I have successfully utilized complex formulas in Google Sheets - including the Let() function. Once I figure out how to reference ranges, the Let() function will be at the core of what I am trying to accomplish.

Here’s a sample from Google sheets:
=if(G6, let(arBarHist,query(googlefinance(C6,“all”,index(NYSEDates, CurrDateOffset-lenShortTerm),CurrDate),“select Col2,Col3,Col4,Col5,Col6 offset 1”,0),arBars,if(MarketOpen,vstack(arBarHist,{googlefinance(C6,“priceopen”),googlefinance(C6,“high”),googlefinance(C6,“low”),googlefinance(C6,“price”),googlefinance(C6,“volume”)}),arBarHist),nBars,rows(arBars),arBarsNoCurr,query(arBars,"select Col1,Col2,Col3,Col4 limit "&nBars-1),maxClose,index(query(arBarsNoCurr,“select max(Col4)”),2),indexMaxClose,match(maxClose,index(arBarsNoCurr,4),0),ValidPB,indexMaxClose<rows(arBarsNoCurr)-2, arPullBack, if(ValidPB,query(arBarsNoCurr,“select * limit “&rows(arBarsNoCurr)-indexMaxClose&” offset “&indexMaxClose),0),sizeEntryBar,index(arBars,nBars,4)-index(arBars,nBars,1),numLargeBars,if(ValidPB,countif(arrayformula(sizeEntryBar/2-abs(index(arPullBack,4)-index(arPullBack,1))),”<0”),0),currSlope,if(ValidPB,sum(query(arBars,"Select Col4 limit 52 offset "&nBars-52))-sum(query(arBars,“Select Col4 limit 52 offset “&nBars-55)),0),{ValidPB,rows(arPullBack),numLargeBars,index(arBars,nBars,5),currSlope}),””)

#4

I didn’t know how to reply to your message. This is just to notify you of my previous response.
Thanks in advance for any light you can shed on this problem.

#5

169718 – Seems like a bug with the LET() formula

#6

162202 – Add QUERY function in Calc like Google Sheets

#7

That is what I suspected. I did do a quick search for a bug report. But, I didn’t find this one. Thx.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.