Great explanation, thank you. I'm getting to grips with the LET function and starting to really like it. However I created a unique list of some items and store in variable and then tried to use the variable in a MakeArray function that also uses Textsplit to have the text over 3 columns and it does not give the right result. If I replace the variable with the spilled dynamic array (#), it works as it should. Any ideas? Data,MAKEARRAY(ROWS(Flex_ul),3,LAMBDA(rw,col,INDEX(TEXTSPLIT(INDEX(Flex_ul,rw,0)," "),1,col))) - where Flex_ul is the unique list.
i treid in google sheet. In one cell I wrote =let(kaina, B2, kaina) - and it shows B2 value - ok. Now in anotehr cell I wrote =kaina and it shows #NAME? . It does not see variable in other cells. I want to define the variable once and use it in other cells. How?
@@ExcelGoogleSheets =IF(AND(OR(B2 ="", B4 =""), "", (ISEVEN (ROW(A2:A), B4-B2, B5-B3)))) The OR Function looks at Cells B2 and B4 for Numbers. If either is Empty or both are empty then Cell D4 is left empty. AND kicks in If Both B2 and B4 contain Numbers and checks the Row Number for Even or Odd. If the ROW is Even, it executes the Formula B4-B2 in Cell D4 (Row 4) . If the ROW is Odd, it executes the Formula B5-B3 in Cell D5 (Row 5) . Lastly, the Formula should have an ARRAY that Auto Populates the Formula down automatically as Numbers are entered in Column B. Essentially, one can Select Row 4 & Row 5 together, each with a different formula and Drag them down by hand...it would be handy if the process could be automated.
@@Puner54 I think you can start by getting rid of the AND. =IF(OR(B2="",B4 =""),"",XXXX) With the next part of the formula, I'm not sure what you are trying to check ISEVEN for. Which row are you try to check is even? The row the formula is in? or the Top row you are using in the formula (B2)? What are you referencing column A for?
@@MuhammadUmair-io5yk... It should look more like this: Cell D4: =ARRAYFORMULA(IF(AND(OR($B$2="",B4=""),"",(ISEVEN(ROW(B2:B),B4-$B$2,B5-$B$3))))) CELL D5: =ARRAYFORMULA(IF(AND(OR($B$2="",B5=""),"",(ISEVEN(ROW(B2:B),B4-$B$2,B5-$B$3))))) Cell D6: =ARRAYFORMULA(IF(AND(OR($B$2="",B6=""),"",(ISEVEN(ROW(B2:B),B6-$B$2,B7-$B$3))))) Cell D7: =ARRAYFORMULA(IF(AND(OR($B$2="",B7=""),"",(ISEVEN(ROW(B2:B),B6-$B$2,B7-$B$3))))) ... and so on down as Data is entered into Cells B2:B.
Excellent instructor👍
Best explanation I've seen of Let function. Thanks!!
You are very very good teacher. Your explanations are crystal clear. Many thanks for your talent.
As always, very good explanation
I want it in Google Sheets so much! Haha
Wow😍 very well explained .. thank u sir it helps a lot to a newbie like Me. 👍
Nice video!
Cool. When will Google sheets get this function?
Great lesson.
Thanks! 😃
That's so cool! Thank you.
I've been wanting this feature for so long
Great explanation, thank you. I'm getting to grips with the LET function and starting to really like it. However I created a unique list of some items and store in variable and then tried to use the variable in a MakeArray function that also uses Textsplit to have the text over 3 columns and it does not give the right result. If I replace the variable with the spilled dynamic array (#), it works as it should. Any ideas? Data,MAKEARRAY(ROWS(Flex_ul),3,LAMBDA(rw,col,INDEX(TEXTSPLIT(INDEX(Flex_ul,rw,0)," "),1,col))) - where Flex_ul is the unique list.
Sorry, but the question is unclear to give an answer. But it should work with LET.
is excel that dump that it repeats the calculation if you do not store it in vairable? it seess same formala, why for it to calculaated twice?
Thanks a lot
Amazing explanation, do you think is possible to program a google spreadsheet custom function to achieve the same?
No. We'll need something from Google Sheets team.
Thanks! Is this not ver similar to LAMDA?
Sort of, it doesn't return a function.
i treid in google sheet. In one cell I wrote =let(kaina, B2, kaina) - and it shows B2 value - ok. Now in anotehr cell I wrote =kaina and it shows #NAME? . It does not see variable in other cells. I want to define the variable once and use it in other cells. How?
Hello Tutor, please its now available on GOOGLE sheet can you do a video on it?
Does let function work the same way in the google sheet as well？
Wow. He literally says in the first minute of the video that google sheets doens't have this function.
You can always use Google app script to create new function such as this LET function.
@@insaniah do you have a script that does what you say?
Pleas google make this formula in spreadsheet
11:50
Sir: Any idea what is Wrong with this Formula:
=IF(AND(OR(B2="", B4=""),"", B4-B2), ((ISODD(A1), B4-B2, B5-B3)))
It's definitely wrong, but then I have no clue what you're trying to accomplish.
@@ExcelGoogleSheets
=IF(AND(OR(B2 ="", B4 =""), "", (ISEVEN (ROW(A2:A), B4-B2, B5-B3))))
The OR Function looks at Cells B2 and B4 for Numbers. If either is Empty or both are empty then Cell D4 is left empty. AND kicks in If Both B2 and B4 contain Numbers and checks the Row Number for Even or Odd. If the ROW is Even, it executes the Formula B4-B2 in Cell D4 (Row 4) . If the ROW is Odd, it executes the Formula B5-B3 in Cell D5 (Row 5) . Lastly, the Formula should have an ARRAY that Auto Populates the Formula down automatically as Numbers are entered in Column B. Essentially, one can Select Row 4 & Row 5 together, each with a different formula and Drag them down by hand...it would be handy if the process could be automated.
@@Puner54 I think you can start by getting rid of the AND.
=IF(OR(B2="",B4 =""),"",XXXX)
With the next part of the formula, I'm not sure what you are trying to check ISEVEN for. Which row are you try to check is even? The row the formula is in? or the Top row you are using in the formula (B2)? What are you referencing column A for?
@@MuhammadUmair-io5yk... It should look more like this:
Cell D4:
=ARRAYFORMULA(IF(AND(OR($B$2="",B4=""),"",(ISEVEN(ROW(B2:B),B4-$B$2,B5-$B$3)))))
CELL D5: =ARRAYFORMULA(IF(AND(OR($B$2="",B5=""),"",(ISEVEN(ROW(B2:B),B4-$B$2,B5-$B$3)))))
Cell D6: =ARRAYFORMULA(IF(AND(OR($B$2="",B6=""),"",(ISEVEN(ROW(B2:B),B6-$B$2,B7-$B$3)))))
Cell D7: =ARRAYFORMULA(IF(AND(OR($B$2="",B7=""),"",(ISEVEN(ROW(B2:B),B6-$B$2,B7-$B$3)))))
... and so on down as Data is entered into Cells B2:B.
=IFS(OR(B2 ="", B4 =""), "", ISEVEN(ROW(A2)), B4-B2,TRUE,B5-B3)