Mike!!!! Dude, I don’t really have a use for that formula but as always you have educated me! I find myself watching your videos just for the knowledge. You always explain how the formula works, thanks again!!!
Dear Sir, I am your great follower and really appreciate your explaining style. I humbly request you to kindly upload a full video for "Power query 2016" as I found it totaly different than 2015. kindly help sir.
very very very helpful Video, thank you for your hard work :) I got one problem may be you will help somehow, when I updated MS to 2016 there are now new formulas :( like =TEXTJOIN, any ideas how to download them?
Good day, your video's has been very helpful so far, but now I'm stuck, I have 2 sheet, the fist has all the cashiers names and amount deposited at the end of day, it also the A.N.D(Amounts Not Deposited) and Surplus. Now my second sheet is the A.N.D/Surplus register. My question, Is there a formula to auto populate the second sheet based only if there is cashiers that is short of surplus (the name only) WITHOUT using a drop down list? Thanks in advance
Dear Respected Sir, I earnestly request you to upload a video on "How to run a macro in a protected sheet without any vba code?" I need it very much........ Please Sir.....
Excel Basics Playlist: th-cam.com/play/PL3FBEE51974F03CCF.html then Full Excel Class: Excel 2016: th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html or Excel 2013: th-cam.com/play/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ.html or Excel 2010: th-cam.com/play/PL706DCCD130378289.html
Dear Sir, my question is not related to this video, I need your help to advise me to choose from all of these videos what is appropriate for intermediate stage. Thanking you in advance
To: Mike , I have checked the n(if(1 formula ：is it first introduced in Chinese forum and found by the Chinese lecture Piny in 2011 ? would you confirm that the n(if(1 could be worked in excel 2003 & 2007 ？
I cannot confirm that "is it first introduced in Chinese forum and found by the Chinese lecture Piny in 2011 ?" As I said in the video, a TH-camr reported that it was true. I heard it second hand, I did not see it myself and therefore I cannot confirm it. n(if(1 should work in any version, but I have not try it in all versions. Bill Szysz tried in in 2010 and I tried it in Excel 2013 and 2016 and it worked.
+ExcelIsFun I have checked that "offset" plus "n(if(1" can increase the power of "offset". Would you introduce more offset videos in future together with n(if(1 ?
HI! I am trying to create range with this method.Using Match function i get the value '2" but the whole result is not converting into range. =sheet!B&(Match(value,array,0):B29 = SHEET!B2:B29 Please suggest
Hi, how to formula a excel. exp: when i type A001 in cell A1, it will show cell E5 Data. when i type A002 in cell A2, it will show cell E10 Data. Hope you can help me. Thank you.
Sir, Do you know someone who can do it (run a macro in protected sheet) without any vba code. Please tell him. I need it very much. I want to make a project which is password protected. So I need it very much sir.
I totally agree. Two points: 1) The MATCH, {00}, IFNA, and MODE.MULT to reduce the resultant array of relative positions and errors down to just a resultant array of relative positions is quite amazing.... 2) It is the most unusual Function Argument Array Operation I have ever seen... And actually, the final point is that after more than 30 years, we all still keep learning really new and amazing ways to use Excel!!!
Yes, maybe so much recondite information/techniques exists because there are so many permutations of mixing all the Excel features/functions (and the underlying code that those of us outside Microsoft do not have access to)!! This is why we all have so much fun every day with Excel! So many new and wonderful things to discover and learn! It is great to be on such an amazing Online Excel team!!! Go Team!!!
My rule is this: If I am not 100% sure that the data is in a Proper Data Set with Field Names, use: ROW(Data)-MIN(ROW(Data))+1 or ROW(Data)-ROW($A$23) +1 But if the data is in a Proper Data Set, then we look at Field Name and use: ROW(Data)-ROW($A$23) I don't think it is necessary to use MIN and ROW because then you have to have Excel Calculation Engine do two calculations, two functions. What is your reasoning for MIN and ROW?
This is really terrific stuff! Wow, way to go LoriM and Mike Gervin. The algorithm already has a use: see here www.mrexcel.com/forum/excel-questions/959397-excel-formula-return-ordered-list-unique-values-criteria.html#post4608370
hey mike, with your help i myself had created a data extraction formula which can substitute advance filters just wanted to share it with you, so can you give me your email id
Mike!!!! Dude, I don’t really have a use for that formula but as always you have educated me! I find myself watching your videos just for the knowledge. You always explain how the formula works, thanks again!!!
You are welcome for the explanation of the Excel formulas!!!! It is always MUCH more fun with an explanation of the hows and whys : ) : )
Woooow! MODE.MULT trick - absolutely awesome!!
Yes, this is a wild, wild formula. Too bad I did not just use Power Query : )
Good to know both ways!
Wow. that is one long formula. EXCELlent explanation Mike.
Glad you liked it, WRH!!!
nice and clear explanation. thanks mike and lori m !!!
You are welcome! Thanks to lori m for posting!
=Sort(Unique(List is very convenient and fast, but somehow I cannot let go of beauties like this !!!
Why do we use index twice ? I tried using it once without the row and it worked
This formula makes my head spin :)
Awesome XL gymnastics
Glad you like the XL gymnastics!!!
Dear Sir, I am your great follower and really appreciate your explaining style. I humbly request you to kindly upload a full video for "Power query 2016" as I found it totaly different than 2015. kindly help sir.
Try my Excel Highline Class Playlist:
people.highline.edu/mgirvin/AllClasses/218_2016/218Excel2016.htm
Videos 3, 20, 21, 22.
Thanks a lot Sir
Amazing trick 👌
Masterpiece !!!
Thanks Mike and lori m !!!
Go Team!!
Good day, please give a hint how to change this formula to make it lookup duplicates only (not unique values) in sorted order?
thank you from egypt ✌
You are welcome!
very very very helpful Video, thank you for your hard work :) I got one problem may be you will help somehow, when I updated MS to 2016 there are now new formulas :( like =TEXTJOIN, any ideas how to download them?
Thanks a lot for this awesome formula.
Thanks to lori m and our amazing Online Excel team!!!
Thanks man really good your videos!!
Good day, your video's has been very helpful so far, but now I'm stuck, I have 2 sheet, the fist has all the cashiers names and amount deposited at the end of day, it also the A.N.D(Amounts Not Deposited) and Surplus. Now my second sheet is the A.N.D/Surplus register. My question, Is there a formula to auto populate the second sheet based only if there is cashiers that is short of surplus (the name only) WITHOUT using a drop down list? Thanks in advance
I am not sure. Try posting question to: mrexcel.com/forum for back and firth dialog to get an Excel solution
Dear Respected Sir, I earnestly request you to upload a video on "How to run a macro in a protected sheet without any vba code?" I need it very much........ Please Sir.....
Hi, i went through your playlist. As a beginner which video do i start with cos i'm just lost
Excel Basics Playlist:
th-cam.com/play/PL3FBEE51974F03CCF.html
then
Full Excel Class:
Excel 2016:
th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
or
Excel 2013:
th-cam.com/play/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ.html
or
Excel 2010:
th-cam.com/play/PL706DCCD130378289.html
Dear Sir, my question is not related to this video, I need your help to advise me to choose from all of these videos what is appropriate for intermediate stage. Thanking you in advance
Why couldn’t we use Small instead of Mode mult?
That is absolutely funny and crazy I can t believe that excel can make these queries without VBA
Funny and Crazy is good!! : )
To: Mike , I have checked the n(if(1 formula ：is it first introduced in Chinese forum and found by the Chinese lecture Piny in 2011 ? would you confirm that the n(if(1 could be worked in excel 2003 & 2007 ？
I cannot confirm that "is it first introduced in Chinese forum and found by the Chinese lecture Piny in 2011 ?" As I said in the video, a TH-camr reported that it was true. I heard it second hand, I did not see it myself and therefore I cannot confirm it.
n(if(1 should work in any version, but I have not try it in all versions. Bill Szysz tried in in 2010 and I tried it in Excel 2013 and 2016 and it worked.
LoriM from TH-cam reported it as true. Just as I said in this video.
+ExcelIsFun thanks for reply. it is because I am vey interesting in the formula "index" plus "n(if(1" to increase the power of index function
+ExcelIsFun I have checked that "offset" plus "n(if(1" can increase the power of "offset". Would you introduce more offset videos in future together with n(if(1 ?
HI!
I am trying to create range with this method.Using Match function i get the value '2" but the whole result is not converting into range.
=sheet!B&(Match(value,array,0):B29 = SHEET!B2:B29
Please suggest
instead of row(data)-row(header), just do rows(data)
Hi, how to formula a excel.
exp: when i type A001 in cell A1, it will show cell E5 Data.
when i type A002 in cell A2, it will show cell E10 Data.
Hope you can help me.
Thank you.
I am not sure. Try posting question to: mrexcel.com/forum
Sir, Do you know someone who can do it (run a macro in protected sheet)
without any vba code. Please tell him. I need it very much. I want to
make a project which is password protected. So I need it very much sir.
this is crazy :)
Yes, but hopefully it is Crazy Fun!!!
this is crazy amazing fun! :D
{0,0} the strangest construction for exact match I have seen. Pretty scary....
I totally agree. Two points: 1) The MATCH, {00}, IFNA, and MODE.MULT to reduce the resultant array of relative positions and errors down to just a resultant array of relative positions is quite amazing.... 2) It is the most unusual Function Argument Array Operation I have ever seen... And actually, the final point is that after more than 30 years, we all still keep learning really new and amazing ways to use Excel!!!
You meant "Scary Good", right?
Absolutely, just makes you wonder how much recondite information exists when we test the powers of Excel.
Yes, maybe so much recondite information/techniques exists because there are so many permutations of mixing all the Excel features/functions (and the underlying code that those of us outside Microsoft do not have access to)!! This is why we all have so much fun every day with Excel! So many new and wonderful things to discover and learn! It is great to be on such an amazing Online Excel team!!! Go Team!!!
Hi Mike why not use this: ROW(Data)-MIN(ROW(Data))+1 instead ROW(Data)-ROW($A$23)
My rule is this:
If I am not 100% sure that the data is in a Proper Data Set with Field Names, use: ROW(Data)-MIN(ROW(Data))+1 or ROW(Data)-ROW($A$23) +1
But if the data is in a Proper Data Set, then we look at Field Name and use: ROW(Data)-ROW($A$23)
I don't think it is necessary to use MIN and ROW because then you have to have Excel Calculation Engine do two calculations, two functions. What is your reasoning for MIN and ROW?
@@excelisfun You're right, i watched your EMT videos, you mostly used ROW(Data)-MIN(ROW(Data))+1 method, I wondered why this is not the case here.
@@pedjanbgd4221 I do not think i have ever used ROW(Data)-MIN(ROW(Data))+1, I always use ROW(Data)-ROW(TopCell)+1 or ROW(Data)-Row(FieldName)
@@excelisfun My mistake, I meant this method ROW(Data)-ROW(TopCell)+1 - (My favorite)
This is really terrific stuff! Wow, way to go LoriM and Mike Gervin. The algorithm already has a use: see here www.mrexcel.com/forum/excel-questions/959397-excel-formula-return-ordered-list-unique-values-criteria.html#post4608370
Awesome!!! That is why we hang out on our awesome Online Excel Team!!!! Go Team!
3:00
: )
Lemon tree
What do you mean by "Lemon Tree"?
hey mike,
with your help i myself had created a data extraction formula which can substitute advance filters just wanted to share it with you, so can you give me your email id