You're very welcome Ivan!) For sure it is, and I wish they could retroactively update old versions of Excel to use them as well - would make life so much easier haha.
This is amazing!!! You can still do this old school buildings for non 365 users. I don´t know how we were able to function without dynamic arrays before. You are a hero!!!!😮 Fot 365 users if we need random between min in B11 and MAX in B12, without repeating , we can use this : =SORTBY(SEQUENCE(B12-B11+1,,B11),RANDARRAY(B12-B11+1)) or =LET(mn,B11,mx,B12,n,ABS(mx-mn)+1,SORTBY(SEQUENCE(n,,MIN(mn,mx)),RANDARRAY(n))) with this does not matter the order in the cells if is 12;7 or 7;12 😀✌✌
hahaha thanks! Excel 365 is like the easy version of Excel now with dynamic arrays, but gosh it is a welcome change! Though, I must admit, I do enjoy the gymnastics of the old formulas :)
@@sjh1956 Cool , but I think you did not understand the problem, the task was to list ALL values between B11 and B12, in a random order , no duplicates. So the size of the array should always have B12-B11+1 elements. Your formula does not follow the fixed nr of results. Thats why you can not use unique.
Dynamic arrays are awesome. Things that were extremely hard or even impossible are now easy and possible It’s cool though to see how we used to do (and still do) things
Thumbs Up!!!I've Been Looking Forward To This Tutorial,Great Explanation/Demonstation Of Building Techniques For Complex Powerful Formulas...Thank You Sir :)
Hi, I'm from Brazil, I discovered this supplement a month ago, but I couldn't understand the explanation. how the editing of the lines works there in the Advanced Formula Environment editor so I created a lambda formula it calculates the BMI the body mass of a person =LAMBDA(HEIGHT; WEIGHT; WEIGHT / (HEIGHT^2)) how do i enter the lines in the Advanced Formula Environment editor?
Interesting, but, as with most of the TH-cam videos on Excel 365, it deals with Lists and the manipulation of data therein I have not found a Excel expert that can explain/demonstrate formula to combine the following math problem into a single cell. The issue seems to be the ability of Excel to reference the product of a calculation and apply other operands to that product, thus producing another product which is also subject to another operand. see example below: The task is cell a) has a fixed number, cell b) has a variable number, cell c) has a number multiplier "multiplies the product of a & b, cell d (is a variable % applied to abc product. Is there a solution or do I have to stay with the format I am using? I have tried: =, format & sum formats, each time throws an excel error? all cells are set to number except the % cell. So, what does this look like in numeric terms a) = 2000. b)= 296 (this is a variable) c) = 10 d) = variable %: Thus calculation is: a*b =12002 (product) *10 = 120002 (product) * variable % say 9.5 product = 6,459,706 No matter what I do, I cannot encapsulate this into a single cell formula, I have to calculate each element separately as above; or, can it be concatenated into a single formula? or am I stuck with a 3-stage process? The problem seems to be related to Excel not wanting to apply additional operands to a product within the formula
Hi Tiz, this sounds like a question for our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment_reply You can upload the sample file that you are working with and it will be much easier to troubleshoot.
I must be missing something here. When we get to the "New List" instead of a list I get #NUM! error. I have compared the formula and it seems to be the same, it just does not work. Can you tell me why?
Not sure if you're the guy who emailed me or not, but anyway, if you are in a previous version of Excel, you should use Ctrl + Shift + Enter to input the formulas. If that doesn't help, go to the forum on TeachExcel.com, upload the exact file that you are working with and an explanation of the issue and it will be much easier to help. Also, include your version of Excel. Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Can you please help me with a formula? I have report of Users and the access they have to various applications. Colomn A has the Access Level Names (names are not unique meaning multiple Users do have the same access level names, in other words it's not a one to one relationship) and columns E - EF have Yes or a dash (-) indicating access to a specific system. I need to identify which access level names in column A have the same access but with different names. Can you or anyone help with that?
looks complicated, I just do not understand the usability of this formula in your video. I Can you explain with examples where this formula is utilized? Planning? Forecasting? Instead, I would like to pulling information from 60 spreadsheets, different columns with just unique title, and copy automatically all the spreadsheets in a master spreadsheet to create pivot tables..... I know it sounds like excel power query however, those videos will definitely help many users.
The words 'Advanced Formula' should have scared me away. I am sure there are people out there that followed you, I think I will go back to beginners class!
Much better with 365 functions!! Thank you for this tutorial Don!
You're very welcome Ivan!) For sure it is, and I wish they could retroactively update old versions of Excel to use them as well - would make life so much easier haha.
This is amazing!!! You can still do this old school buildings for non 365 users. I don´t know how we were able to function without dynamic arrays before. You are a hero!!!!😮
Fot 365 users if we need random between min in B11 and MAX in B12, without repeating , we can use this :
=SORTBY(SEQUENCE(B12-B11+1,,B11),RANDARRAY(B12-B11+1))
or
=LET(mn,B11,mx,B12,n,ABS(mx-mn)+1,SORTBY(SEQUENCE(n,,MIN(mn,mx)),RANDARRAY(n)))
with this does not matter the order in the cells if is 12;7 or 7;12 😀✌✌
hahaha thanks! Excel 365 is like the easy version of Excel now with dynamic arrays, but gosh it is a welcome change! Though, I must admit, I do enjoy the gymnastics of the old formulas :)
Another "easier" formula is: =SORT(UNIQUE(RANDARRAY($B$10;;$B$11;$B$12;TRUE))),
where B10 is the size of the array, and B11 & B12 is Min and Max.
@@sjh1956 Cool , but I think you did not understand the problem, the task was to list ALL values between B11 and B12, in a random order , no duplicates. So the size of the array should always have B12-B11+1 elements. Your formula does not follow the fixed nr of results. Thats why you can not use unique.
Thank you very much, very clear and very informative
Thanks Michel! I'm glad you think so)
I love this kind of Formulas. I could sit there for hours and understand how this is working.
Dynamic arrays are awesome. Things that were extremely hard or even impossible are now easy and possible
It’s cool though to see how we used to do (and still do) things
I totally agree! I feel like 365 is what is should have been 10 years ago lol.
Thumbs Up!!!I've Been Looking Forward To This Tutorial,Great Explanation/Demonstation Of Building Techniques For Complex Powerful Formulas...Thank You Sir :)
I'm glad you like it Darryl! You are very welcome!)
Hi, I'm from Brazil, I discovered this supplement a month ago, but I couldn't understand the explanation.
how the editing of the lines works there in the Advanced Formula Environment editor so I created a lambda formula it calculates the BMI the body mass of a person
=LAMBDA(HEIGHT; WEIGHT; WEIGHT / (HEIGHT^2))
how do i enter the lines in the Advanced Formula Environment editor?
Interesting, but, as with most of the TH-cam videos on Excel 365, it deals with Lists and the manipulation of data therein I have not found a Excel expert that can explain/demonstrate formula to combine the following math problem into a single cell. The issue seems to be the ability of Excel to reference the product of a calculation and apply other operands to that product, thus producing another product which is also subject to another operand. see example below:
The task is cell a) has a fixed number, cell b) has a variable number, cell c) has a number multiplier "multiplies the product of a & b, cell d (is a variable % applied to abc product.
Is there a solution or do I have to stay with the format I am using? I have tried: =, format & sum formats, each time throws an excel error? all cells are set to number except the % cell.
So, what does this look like in numeric terms a) = 2000. b)= 296 (this is a variable) c) = 10 d) = variable %: Thus calculation is: a*b =12002 (product) *10 = 120002 (product) * variable % say 9.5 product = 6,459,706
No matter what I do, I cannot encapsulate this into a single cell formula, I have to calculate each element separately as above; or, can it be concatenated into a single formula? or am I stuck with a 3-stage process? The problem seems to be related to Excel not wanting to apply additional operands to a product within the formula
Hi Tiz, this sounds like a question for our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment_reply You can upload the sample file that you are working with and it will be much easier to troubleshoot.
I must be missing something here. When we get to the "New List" instead of a list I get #NUM! error. I have compared the formula and it seems to be the same, it just does not work. Can you tell me why?
Not sure if you're the guy who emailed me or not, but anyway, if you are in a previous version of Excel, you should use Ctrl + Shift + Enter to input the formulas. If that doesn't help, go to the forum on TeachExcel.com, upload the exact file that you are working with and an explanation of the issue and it will be much easier to help. Also, include your version of Excel.
Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Can you please help me with a formula? I have report of Users and the access they have to various applications. Colomn A has the Access Level Names (names are not unique meaning multiple Users do have the same access level names, in other words it's not a one to one relationship) and columns E - EF have Yes or a dash (-) indicating access to a specific system. I need to identify which access level names in column A have the same access but with different names. Can you or anyone help with that?
looks complicated, I just do not understand the usability of this formula in your video. I Can you explain with examples where this formula is utilized? Planning? Forecasting? Instead, I would like to pulling information from 60 spreadsheets, different columns with just unique title, and copy automatically all the spreadsheets in a master spreadsheet to create pivot tables..... I know it sounds like excel power query however, those videos will definitely help many users.
The words 'Advanced Formula' should have scared me away. I am sure there are people out there that followed you, I think I will go back to beginners class!