You are the best Excel teacher I have seen in my life
@excelisfun
Жыл бұрын
Glad to help : )
@excelisfun
4 жыл бұрын
Topics: 1. (00:00) Introduction. 2. (00:33) Why LET is useful. 3. (01:25)Data Validation for variable cells. 4. (01:39) Dynamic Report Label. 5. (02:02) Row Variable Formula. 6. (05:03) Column Variable Formula. 7. (05:39) Single Cell Report Formula. 8. (14:09) Summary and Video Links.
@GeertDelmulle
4 жыл бұрын
Yay! Crazy Array formulas are back!... because they never left! :-) And you, Mike, are the Master! Wow! The result is awesome! And so is that formula! BTW: in this case I’ll take the pivot table any day of the week.Just way simpler. That’s not to say that I don’t love the new calc engine: I wouldn’t want to live without it...
@excelisfun
4 жыл бұрын
Yes, most of us will use a PivotTable, but I have heard of a few people are using formulas just like this for reports and invoicing that they say is perfect!
@GeertDelmulle
4 жыл бұрын
Well Mike, I “somehow” feel that those people owe you a debt of gratitude. Big time. :-)
@excelisfun
4 жыл бұрын
@@GeertDelmulle I own them too - since I originally learned this from David Milbrandt : ) : ) Go Team!!!!!!
@ricos1497
4 жыл бұрын
@@excelisfun Mike/Geert, I can see the attractiveness of something like this. The thing about LET functions is that they are reusable. For this one, just copy it into a workbook, change the source of the drop down lists, the source data and the column and you're there. If you're really smart you can even add subtotals. Formatting is obviously the biggest issue, but then it isn't that great on pivot tables. However, the most amazing thing for me is what it shows can be done. I've done LET functions for a few things now, and each time I've tried to make them as close to reusable as possible (and suceeded on most occasions). If we can write a function that always returns a table with row and column totals based on a data source, a column to SUM, a row and a column field, then Excel can do it. If Excel can do it, and it's repeatable and provable, then its really then up to Microsoft to provide that function as standard (or we can certainly build that case). For example, I've built a SPLIT() function for something I was doing, and a TEXTTOCOLUMNS() function. Now we have a PIVOT() function, and I have done an UNPIVOT() function (sort of) all in LET that can be re-used just by changing the initial variables as you would any other function. The LET function simply takes in variables and performs an algorithm. There is no reason that these can't be shared, proved and then voted on for microsoft to add to it's growing list. I vote for PIVOT(DataSource, ColumnField, RowField, ValueField, IncludeHeaders, IncludeTotals) as a function! Which we can now do based on Mike's function, or my one I added elsewhere in the comments! Easy!
@djstarr8
2 жыл бұрын
I was blown away by this!!! Thank you. I took a step further by using sort by to put the most relevant rows and columns at the top left of the report!
@simfinso858
4 жыл бұрын
Oh my God.This video should be Labeled as How to Make Pivot Table without using pivot table.Super Duper Awesome video.
@excelisfun
4 жыл бұрын
That is a good idea : )
@MalinaC
4 жыл бұрын
Fortunately LET exists ;). Thumbs up!
@bonmonil
4 жыл бұрын
Where was this video 2 years ago? Mind-blowing stuff. Thanks a lot 👍👍👍
@excelisfun
4 жыл бұрын
LET was not around two years ago.
@wayneedmondson1065
4 жыл бұрын
Wow!! Blown away by this one! Thanks for a master class on how to bend Excel Functions and Formulas to do your bidding. Absolute Monday Madness!! Thumbs up!!
@excelisfun
4 жыл бұрын
It is wild madness : ) Glad it was mad for you, Wayne : )
@iankr
Жыл бұрын
Brilliant! Thanks, Mike.
@excelisfun
Жыл бұрын
You are welcome! Here is a newer video with the new functions VSTACK and HSTACK: kzitem.info/news/bejne/km2LnpNsm5ekrH4
@chrism9037
4 жыл бұрын
Can’t wait to get this in my MS 365. Nice video Mike!!
@excelisfun
4 жыл бұрын
I can't wait either : ) : )
@ExcelVbaIsFun
4 жыл бұрын
Holy Amazing Excel Functions, Batman!! That was FUN!
@excelisfun
4 жыл бұрын
You are batman welcome!
@Fxingenieria
2 ай бұрын
Excelent, thanks EXCELISFUN
@excelisfun
2 ай бұрын
You are welcome!!!!
@HusseinKorish
4 жыл бұрын
Wow ... that's amazing Mike ....every difficult solution is easy with you .
@excelisfun
4 жыл бұрын
Glad I can help with making it more easy : )
@michaelbrown8821
4 жыл бұрын
Just....WOW! NIce job Mike.
@excelisfun
4 жыл бұрын
Glad you like it, Michael !!!!
@vhc6600
4 жыл бұрын
Mike just awesome thanks for doing the video you are amazing :) Not only is the calculation more efficient but if you need to amend a variable later you only need to do it once! 👍
@excelisfun
4 жыл бұрын
Yes, that is so true. In fact I did exactly that multiple times as I created the formula : ) : )
@sevagj.b
4 жыл бұрын
WOW Mike, I need to watch the video at least 10 times
@excelisfun
4 жыл бұрын
I am glad you like it, Sevag!!!
@edge5817
4 жыл бұрын
WOW! just WOW!...Thanks Mike. Can't wait to have the LEN() function soon!
@excelisfun
4 жыл бұрын
I can't wait for you to get LET, either : )
@hassanjatta4257
4 жыл бұрын
Amazing piece of excel skill!
@excelisfun
4 жыл бұрын
Yes indeed. Thanks, Hassan!!!
@realtoast7036
2 жыл бұрын
Mike, I am making amazing use of this lesson! Thank you! Question: can a third criteria selector be added? Specifically, include the date column as criteria, so User can do everything done here, but limit the report to the date range selected (within the LET function or from with in the same cell as the LET function)?
@markpodesta4605
4 жыл бұрын
Good one. Looks like a very complex formula.
@excelisfun
4 жыл бұрын
VERY. Not necessary 99% of the time, but for the 1% it is perfect : )
@vida1719
4 жыл бұрын
That was a great use of new Excel functions! However, Microsoft must return F9 in Let Function to quickly check intermediate results
@excelisfun
4 жыл бұрын
I HATE that F9 does not work, and that the current version does not allow variables in drop down. It makes the LET function in the current version almost useless. It is just too hard with those bugs.
@henryg5735
4 жыл бұрын
@@excelisfun Sometimes it is easier to create the old way then convert to use Let via search and replace. Let can make some formulas so much easier to review later that it is worth the double-effort. PS my brain is still aching from following this video lol
@excelisfun
4 жыл бұрын
@@henryg5735 That is true.
@CarlosMartinez-mo8iq
4 жыл бұрын
Hey Mike, I remember that a few videos ago you talked about this formula to remove numbers from a cell. it's not the best formula ever, but I think this might work. You may use this formula {=TEXTJOIN("",TRUE,IFERROR(IF(ISNUMBER(NUMBERVALUE(MID(A5,ROW(INDIRECT("1:100")),1))),"",MID(A5,ROW(INDIRECT("1:100")),1)),""))} this will remove all numbers. However, if you'd like to remove text and leave numbers, you may use this one {=TEXTJOIN("",TRUE,IFERROR(MID(A5,ROW(INDIRECT("1:100")),1)+0,""))}
@excelisfun
4 жыл бұрын
Thanks for the cool formulas : )
@pathakprathamesh
2 жыл бұрын
Thanks, Mike! I further wanted the transposed column headers to be a part of the single cell report. I was able to append the two (column headers and the output below) by just joining the two arrays with a colon (K2#:K3#) in a separate cell! However, if I try to put that in LET or any other formula, like CHOOSE({1:2},firstarray#,secondarray#), it is not appending without errors. Your valuable thoughts please!
@Al-Ahdal
2 жыл бұрын
Boss, how to use VSTACK, HSTACK simultaneously to bring in all row headers and column headers in this video and use single formula. Kindly guide on this. Thank you in advance.
@stephenbrincat7124
4 жыл бұрын
Wow Mike, this is mnd blowing, thansk for sharing, you're the best 👍👍👍
@excelisfun
4 жыл бұрын
You are welcome, Stephen!
@darrylmorgan
4 жыл бұрын
Boom!WOW Completely Blown Away What An Awesome Formula...Thank You Mike :)
@excelisfun
4 жыл бұрын
Boom is for this video boomeranged back in new form : ) : )
@nonoobott8602
4 жыл бұрын
This is amazing Mike. I need the LET function in my MS Excel 365. Thanks for sharing
@excelisfun
4 жыл бұрын
You are welcome for the share, Nono!!!
@mikelennon1078
3 жыл бұрын
Can DGET be used to return multiple values based on a cell value from the same or another worksheet. ( One to many) Lets say we have companies and would like to see the list ( records) of all employees of a selected company with names, titles, email addresses etc.? Also is it possible that these records can be edited/updated right after showing up/ filtering? If not, what would you recommend to use?
@sasavienne
4 жыл бұрын
Thanks Mike. This video is really great and useful.
@excelisfun
4 жыл бұрын
Glad you like it, K D!!!!
@patrickschardt7724
4 жыл бұрын
I’ve been using LET in almost all my formulas lately. It’s so helpful. Great video. I always name the last formula/calculation as Result/RES for clarifications I have noticed versions beyond 13001.20266 on build 2006 do not show the variable names the autocomplete functionality like named ranges, formulas, and table names do. I haven’t checked build 2007 version 13029.20308 yet but the beta version of 2007 a few weeks didn’t work either
@excelisfun
4 жыл бұрын
Yes, my version is not showing variable name, although earlier versions did show it.
@patrickschardt7724
4 жыл бұрын
ExcelIsFun what version do you have? The variables weren’t displayed when LET was first released. That functionality was added in a later version. Not sure if it’s been taken out or temporarily disabled. I wish it had color like table names and names ranges. Also I wish table references from other sheets or workbooks had color too
@patrickschardt7724
4 жыл бұрын
ExcelIsFun I can confirm the LET function does not show variable names in the autocomplete in build 2007 version 13029.20308. Just checked. Reverting back to build 2006 version 13001.20266 for now
@excelisfun
4 жыл бұрын
@@patrickschardt7724 That is smart to revert back : ) : )
@Al-Ahdal
2 жыл бұрын
Boss, kindly make a video, solving the same by using VSTACK, HSTACK..... i.e. using single formula. Thanks in Advance,
@paulsingleton6071
4 жыл бұрын
Absolutely outstanding Mike, thank you 👍
@excelisfun
4 жыл бұрын
You aer welcome, Paul : ) : )
@kishorpanara4191
4 жыл бұрын
This is amazing mike
@excelisfun
4 жыл бұрын
Glad it is amazing for you, kishor!!!
@DavidNBerger
4 жыл бұрын
Thanks Mike!
@excelisfun
4 жыл бұрын
You are welcome, David!!!!
@davorercegovac3674
4 жыл бұрын
Just Wow❗️❗️❗️Amazing 👍
@excelisfun
4 жыл бұрын
Glad it is amazing for you, Davor : )
@johnborg5419
4 жыл бұрын
Thanks Mike. This is a MasterPiece!!! FUN FUN FUN : ) : )
@excelisfun
4 жыл бұрын
Glad you like the formula FUN, FUN, FUN!!!!
@kingandfana
4 жыл бұрын
Hi i found your Videoa very helpfull but i have a question can i subtract multiple cells from one cell for example B1:B10 from A1
@excelisfun
4 жыл бұрын
Yes. That would be an array subtraction calculation : )
@kingandfana
4 жыл бұрын
@@excelisfun kindly can you tell me the whole process how do i do it
@excelisfun
4 жыл бұрын
@@kingandfana =A1-B1:B10
@spilledgraphics
4 жыл бұрын
01:09 .... what!! Mike I am honored to have inspired for this video to be made! WOW!! I am still waiting for my Excel version to have =LET() .... I am not part of the Insider Program becuase of compatability issues I had before ... : (
@excelisfun
4 жыл бұрын
Go Team!!!!!!!
@spilledgraphics
4 жыл бұрын
Yet, since I don´t have the LET(), I would have indent the formula this way. (forgive me for my "pendantic-ness"!!) For the row header: =LET( RowVariableUniqueList, UNIQUE( XLOOKUP( K2, fSalesAnswer[[#Headers], [Customer]:[Product]], fSalesAnswer[[Customer]:[Product]] ) ), CountRowVariableUniqueList, ROWS(RowVariableUniqueList), IF( SEQUENCE( CountRowVariableUniqueList+1 ) > CountRowVariableUniqueList, "Total", SORT(RowVariableUniqueList) ) ) For the column header: =LET( ColumnVariableUniqueList, UNIQUE( XLOOKUP( K3, fSalesAnswer[[#Headers], [Customer]:[Product]], fSalesAnswer[[Customer]:[Product]] ) ), CountColumnVariableUniqueList, ROWS( ColumnVariableUniqueList ), TransposedColumnVariableUniqueList, TRANSPOSE( SORT( ColumnVariableUniqueList ) ), IF( SEQUENCE( , CountColumnVariableUniqueList+1)> CountColumnVariableUniqueList, "Total", TransposedColumnVariableUniqueList ) ) And the for magic formula!!!: =LET( Records, fSalesAnswer[[Customer]:[Product]], FieldNames, fSalesAnswer[[#Headers],[Customer]:[Product]], RevColumn, fSalesAnswer[Revenue], RowUniqueList, J7#, ColumnUniqueList, K6#, CountRowUniqueList, ROWS( RowUniqueList ), CountColumnUniqueList, COLUMNS( ColumnUniqueList ), LookupColumnForRowVariable, XLOOKUP( K2, FieldNames, Records), LookupColumnForColumnVariable, XLOOKUP( K3, FieldNames, Records), IF( RowUniqueList & ColumnUniqueList="TotalTotal", SUM(RevColumn), IF( SEQUENCE(, CountColumnUniqueList) > CountColumnUniqueList-1, SUMIFS( RevColumn, LookupColumnForRowVariable, RowUniqueList ), IF( SEQUENCE( CountRowUniqueList) > CountRowUniqueList-1, SUMIFS( RevColumn, LookupColumnForColumnVariable, ColumnUniqueList ), SUMIFS( RevColumn, LookupColumnForColumnVariable, ColumnUniqueList, LookupColumnForRowVariable, RowUniqueList ) ) ) ) )
@spilledgraphics
4 жыл бұрын
Funny fact: I don´t write code or develop VBA, I just like seeing formulas written this way to rapidly spot where the issue is. :P
@MadhawaAbhayaratne
Жыл бұрын
Thank you for a lovely challenge. Here's single cell formula I was able to come up with for this problem: =LET( VR,INDIRECT("fSales["&K2&"]"), UVR,SORT(UNIQUE(VR)), VC,INDIRECT("fSales["&K3&"]"), UVC,SORT(UNIQUE(VC)), Sum,SUMIFS(fSales[Revenue],VR,UVR,VC,TRANSPOSE(UVC)), TR,BYROW(Sum,LAMBDA(row,SUM(row))), TC,BYCOL(Sum,LAMBDA(col,SUM(col))), Total,REDUCE(0,Sum,LAMBDA(i,cell,i+cell)), RHeaders,EXPAND(UVR,COUNTA(UVR)+1,,"Total"), CHeaders,TRANSPOSE(EXPAND(UVC,COUNTA(UVC)+1,,"Total")), Corner,K2&"/"&K3, Output,HSTACK(VSTACK(Corner,RHeaders),VSTACK(CHeaders,HSTACK(VSTACK(Sum,TC),VSTACK(TR,Total)))),Output)
@amit12000
4 жыл бұрын
Great Mike
@excelisfun
4 жыл бұрын
Glad you like it, Amit!!!
@venusnewton1669
4 жыл бұрын
Hi, I think the “TotalTotal sum() “gives the unconditional total amount, not specific to “cross table’s” condition
@excelisfun
4 жыл бұрын
The total total and cross tab total are the same when you have the row area and column area unique list formulas : )
@filipbaraka468
4 жыл бұрын
Hey how are you today sir? can u recomended a video where i can make searcheble drop down list. i have 4000 items and idea was to make a search bar on top few cels and under is all my product that i selling. but i want when customer tipe few ford of items that he looking for under the search bar made a list of all items that contain that leather.. i hope you understand my bad english. chears
@JonathanExcels
4 жыл бұрын
Amazing
@excelisfun
4 жыл бұрын
Glad it is amazing for you. P.S. I still think I like the old way better...
@dirv3247
4 жыл бұрын
Help!!! Do you have a video with retention codes for a warehouse. Example code 94-1 will add 2 years to a box received in 2014 making the destruction date 2016. Also I have over 300 codes with same/different years for each.
@excelisfun
4 жыл бұрын
I am not sure. try mrexcel.com/forum
@dirv3247
4 жыл бұрын
Hey thanks!!!
@mdtechpk739
4 жыл бұрын
Wow, amazing
@excelisfun
4 жыл бұрын
Glad it is amazing for you, MD Tech!!!
@InspiredThroughLiving
4 жыл бұрын
Very interesting, how do I create a power query for a table that contains numbers and text? The numbers contain an employee number that has a letter in it which cannot be removed and it's driving me nuts!
@excelisfun
4 жыл бұрын
Can you convert it all to text?
@excelisfun
4 жыл бұрын
You can't have mixed data in a column
@InspiredThroughLiving
4 жыл бұрын
@@excelisfun ok thank you, I will try that! I'm trying to streamline my data because I keep making mistakes with my data.
@Hey_Delight
4 жыл бұрын
💖💚💗💙💟
@excelisfun
4 жыл бұрын
Thanks for the love : )
@Hey_Delight
4 жыл бұрын
@@excelisfun Thank you Sir Mike.
@ExceliAdam
4 жыл бұрын
I remember your old formula without XLOOKUP. Now it is easier with XLOOKUP, and it occurred to me to put "*" instead of "Total" and then summing up is very simple. formula for list for data from 1528: =IF(SEQUENCE(,COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0))))+1)>COUNTA(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0)))), "*",TRANSPOSE(SORT(UNIQUE(INDEX(fRevenue5,,MATCH(I5,fRevenue5[#Headers],0)))))) And sum: =SUMIFS(fRevenue5[Revenue],XLOOKUP(I4,fRevenue5[#Headers],fRevenue5),H10#,XLOOKUP(I5,fRevenue5[#Headers],fRevenue5),I9#) P.S. I use conditional formatting to show "Total" instead of "*"
@excelisfun
4 жыл бұрын
Very cool trick! Thanks, Teammate : )
@vloutas
4 жыл бұрын
Total Crazy
@alwarhi
4 жыл бұрын
I love you 😘 man
@excelisfun
4 жыл бұрын
Glad you love the videos! I love you to, Teammate!!
@ricos1497
4 жыл бұрын
At the risk of sounding smug, I think I can go one better, by including the whole table in one formula: =LET(rowVar,K2, colVar,K3, sumCol,fSales[Revenue], tblHdr,rowVar&"/"&colVar, xValsAll,XLOOKUP(colVar,fSales[#Headers],fSales[#Data]), yValsAll,XLOOKUP(rowVar,fSales[#Headers],fSales[#Data]), xHdrs,SORT(UNIQUE(xValsAll)), yHdrs,SORT(UNIQUE(yValsAll)), cntX,ROWS(xHdrs)+2, cntY,ROWS(yHdrs)+2, i,SEQUENCE(cntY,cntX), x,IF(MOD(i,cntX)=0,cntX,MOD(i,cntX)), y,ROUNDUP(i/cntX,0), xVal,INDEX(xHdrs,x-1,1), yVal,INDEX(yHdrs,y-1,1), results,SWITCH(TRUE,x*y=1,tblHdr,((x
@ricos1497
4 жыл бұрын
To add some explanation, the trick is to use a 2 dimensional sequence rather than just a single column, which represents the table with a sequence of numbers. If you try stepping through the above formula, when you return "i" with a row variable of Product and column variable of Region, you get a 4 row, 6 column sequence table, which is what you'd expect as there are 4 products, plus 2 columns for header and totals. There are 2 Regions, plus 2 for header/totals. That's your table mapped, but with a number sequence 1-24 instead of values. As it's dynamic, you need to know what your x and y coordinates are for each instance of "i" and return the value for that coordinate. The MOD formula will get the x value (column number) and the ROUNDUP the y value (row number) (you can test these both in the LET function to prove they return correctly. Finally, use the SWITCH formula to define what is returned for each position in the sequence table. For example ((x
@ricos1497
4 жыл бұрын
And for more information! Here is a LET function that determines where you are in a table based on the number of rows and columns. Simply change the maxCol,6+2 and maxRow,2+2 to 4+2 and 3+2 or whatever to show the movement of the pivot table (the +2 is to add a column/row for both header and total). It's quite a good illustration, and you would simply add in your data source XLOOKUPS and the SUMIFS formulas in place of Values and Row Total sections in the SWITCH formula: =LET(maxCol,6+2, maxRow,2+2, i,SEQUENCE(maxRow,maxCol), x,IF(MOD(i,maxCol)=0,maxCol,MOD(i,maxCol)), y,ROUNDUP(i/maxCol,0), result,SWITCH(TRUE,x*y=1,"Header",((x
@Excelambda
4 жыл бұрын
@@ricos1497 Really Great Work! The trick is..you dnt need 2 dim. seq arrays at all. Excel fills the gaps. for example put this in any cell and check the result =SWITCH(TRUE,SEQUENCE(,3)=1,{1;2;3;4},SEQUENCE(,3)=3,{"a";"b";"c";"d"}). See? 2 dim array 4 by 3 with the left and right column added using one dim sequences. The same way you can do, top row , bottom row etc. For TRUE you leave the core array that fills itself, no conditions needed, so for all conditions for creating the perimeter of array , left and right column, top and bottom row, only one dim sequence array You used SWITCH , I used IFS, more ore less the same. I used very simple 2 dim sequence Only for the corners.
@ricos1497
4 жыл бұрын
@@Excelambda yes, great suggestion. I always forget about IFS() too, that'd be nicer than switch. I'll have to get the laptop out again and check out your idea. Fantastic.
@ricos1497
4 жыл бұрын
@@Excelambda I've had a quick test of your suggestion, and I understand it, but I'm not entirely sure how to apply it to the example in the video. Do you have your solution/formula? I'd be quite interested to see it in action.
@weert00
Жыл бұрын
Is anyone able to give me a hand with a similar topic, I cannot quite figure out the syntax. Say for instance I have a table like this: Plant | Material | Q1 Price | Q2 Price | Q3 Price | Q4 Price | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | I would like the result of my new array using Let to Look like this: Plant | | Q1 | Q2 | Q3 | Q4 | I was able to use the basic ideas in this video to write something like this =LET( List,UNIQUE(Forecast[Plant]), REVCOL,Forecast[Jan], REVCOL2,Forecast[Feb], TEST,SUMIFS(REVCOL,Forecast[Plant],List), CHOOSE({1,2},List,TEST)) This results in a table Plant | Jan which correctly summarizes the January volumes by plant -- Now it is time to make a more complex calculation for instance I would normally use: =SUM(Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=$A1)) using a helper unique list to allow me to summarize what is essentially a sum product function, but I am trying to make that one of the calculations in my LET, such that my end result can be the following, allowing me to control a summary table layout: =LET( List,Unique(Forecast[Plant]) Q1,Filter((Forecast[Jan]+Forecast[Feb]+Forecast[Mar])*Forecast[Q1],Forecast[Plant]=List), Q2,Filter((Forecast[Apr]+Forecast[May]+Forecast[Jun])*Forecast[Q2],Forecast[Plant]=List), Q3,Filter((Forecast[Jul]+Forecast[Aug]+Forecast[Sep])*Forecast[Q3],Forecast[Plant]=List), Q4,Filter((Forecast[Oct]+Forecast[Nov]+Forecast[Dec])*Forecast[Q4],Forecast[Plant]=List), Choose({1,2,3,4,5,6},Plant,"",Q1,Q2,Q3,Q4)) Appreciate the help in advance here
@ExcelInstructor
3 жыл бұрын
14:11 I do leave comments, alot of them :)
@excelisfun
3 жыл бұрын
I can't get to all of them, but they HELP the Team Alot!!!! Thanks for helping the Team, Radoslaw : ) : ) : ) : )
@ExcelInstructor
3 жыл бұрын
@@excelisfun im trying :) I also read upon how to get MVP :) and weirdly enough I do contribute on ExcelForum.pl website, I teach excel team I work with in my work, and now here i I find a way to help. Maybe even I couldcontribute to your videos somehow :) (altho on't know what I could do :))
@MrStonecold71
4 жыл бұрын
How can we anoint you as as the Excel God? Just tell me. I'm all ears!
@excelisfun
4 жыл бұрын
No Gods here ; ) Just a Team! I make and post videos and Excel files and you watch, learn and thumbs up and comment. Go Team!!!!
@kasiasama7322
4 жыл бұрын
It is too complicated for me. I don't seem to have a talent for excel :(
@excelisfun
4 жыл бұрын
No, no, no..... !!!!! Of course you have talent for Excel : ) : ) This sort of formula is at the outer extreme. Few people do this sort of thing. Forget this sort of stuff and just have fun with Excel that way you have fun with Excel, Kasia : ) : ) : ) : )
@kasiasama7322
4 жыл бұрын
Thank you so much for your encouraging reply. :) :)
@moharirshyamsunder4879
3 жыл бұрын
Dokywarun gela Mike, sorry...
@jeanpascaldavy2795
4 жыл бұрын
=LET(MikeGirvin,"Great, Great, Great!",...
@excelisfun
4 жыл бұрын
Thanks, jean : ) : )
@Shortinwithme
4 жыл бұрын
Please can you speak hindi
@nileshshah7728
Жыл бұрын
I used this one :) =LET(a,SORT(UNIQUE(XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer))),b,TRANSPOSE(SORT(UNIQUE(XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer)))),c,SUMIFS(fSalesAnswer[Revenue],XLOOKUP(K2,fSalesAnswer[#Headers],fSalesAnswer),a,XLOOKUP(K3,fSalesAnswer[#Headers],fSalesAnswer),b),d,BYCOL(c,LAMBDA(q,SUM(q))),e,BYROW(c,LAMBDA(r,SUM(r))),VSTACK(HSTACK(K2&"/"&K3,b,"Total"),HSTACK(a,c,e),HSTACK("Total",d,SUM(d))))
Пікірлер: 152