Great CF tricks✌🏼 There are other million ways formulas and formats possible for 1/2 years. The idea is that whatever formula delivers a convenient helper column can be used as field arguments with GROUPBY/PIVOTBY. 😉
@@excelisfun For the ones that still do not have groupby/pivotby this is my take: (again YT does not post stuff, only if we hit next to " n Comments" header Sort by -> Newest first =LET( d, InvoiceSales35[Date], a, InvoiceSales35[InvoiceAmount], t, YEAR(d) & ", " & IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"), u, UNIQUE(SORT(t)), m, MAP(u, LAMBDA(x, SUM((x = t) * a))), VSTACK({"1/2 Year", "Sum of InvoiceAmount"}, HSTACK(u, m), HSTACK("Total", SUM(m))) ) =LET( h, "Total", d, InvoiceSales35[Date], a, InvoiceSales35[InvoiceAmount], y, YEAR(d), t, IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"), u, UNIQUE(SORT(y)), v, TOROW(UNIQUE(SORT(t))), m, MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))), VSTACK(HSTACK("", v, h), HSTACK(u, m, BYROW(m, SUM)), HSTACK(h, BYCOL(m, SUM), SUM(m))) ) apart of cosmetics main values results are 2 simple MAP constructions MAP(u, LAMBDA(x, SUM((x = t) * a))) and, MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))),
Good. Several ways to get the helper column. Several ways to summarize the data. In this situarion I prefer ROUNDUP to fill the helper and GROUPBY/PIVOTBY for the summarization. My general Excel "rule": formulas over features; Tables over ranges; simplicity over complexity; keyboards over mice.
I'm sure you already know this, but watching your video inspired me to try using the spill operator in the conditional formatting "Applies to" and it "worked". But sadly when going back into edit the rule, Excel has replaced the dynamic spill with the hardcoded address :( Perhaps MS could fix this in the future to help us having to stop guessing when cells to apply the CF to.
Thanks for the excellent videos. Has anyone told you that you are a voice talent too? You have the exact same voice as Ross Geller from friends. I can imagine having my answering machine with a personalized message in your voice! $$$$ 😀
Great video MIKE thanks! ... ...I am still waiting for MS .... to release these features in the stable version ... I have a question .... recently a parameter was added that I don't understand to both functions .... do you know anything about its use?
Yes, the new feature allows you to have report as it is now, or in table format. You can try it: put two conditions in rows and try both parameters : )
Well... We have no idea. Why? Because usually the beta appears in M 365 in a few months. BUT: these two functions have already been over a year!!!! I think that there were some programming issues and so it is the new world record for longest beta period ever. But, when then get it right: WOW!!! It will be a game changer : )
Nice trick, but i wonder what is going on because I know GROUPBY and PIVOTBY even lambda are python function(pandas) and they also add python in excel, is it start of excel to derive to python language? 🤔🤔
As far as I know, there is not an easy way like in the worksheet. Maybe someone else knows? Otherwise, I will try a few things and see if I can figure it out : )
Well... We have no idea. Why? Because usually the beta appears in M 365 in a few months. BUT: these two functions have already been over a year!!!! I think that there were some programming issues and so it is the new world record for longest beta period ever. But, when then get it right: WOW!!! It will be a game changer : )
Thanks Mike for a great video.👍 While waiting for the GROUPBY and PIVOTBY functions, we can do this task using functions available to everyone (Excel 365). For GROUPBY it would be something like this =LET( dates, InvoiceSales35[Date], values, InvoiceSales35[InvoiceAmount], year_half, YEAR(dates) &", " & IF(MONTH(dates)
Great CF tricks✌🏼
There are other million ways formulas and formats possible for 1/2 years. The idea is that whatever formula delivers a convenient helper column can be used as field arguments with GROUPBY/PIVOTBY. 😉
You are soooooooooooooooooooo right: just make a helper column and then use it in GROUPBY or PIVOTBY. Thanks, Wingman Teammate : ) : ) : )
@@excelisfun For the ones that still do not have groupby/pivotby this is my take: (again YT does not post stuff, only if we hit next to " n Comments" header Sort by -> Newest first
=LET(
d, InvoiceSales35[Date],
a, InvoiceSales35[InvoiceAmount],
t, YEAR(d) & ", " & IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"),
u, UNIQUE(SORT(t)),
m, MAP(u, LAMBDA(x, SUM((x = t) * a))),
VSTACK({"1/2 Year", "Sum of InvoiceAmount"}, HSTACK(u, m), HSTACK("Total", SUM(m)))
)
=LET(
h, "Total",
d, InvoiceSales35[Date],
a, InvoiceSales35[InvoiceAmount],
y, YEAR(d),
t, IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"),
u, UNIQUE(SORT(y)),
v, TOROW(UNIQUE(SORT(t))),
m, MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))),
VSTACK(HSTACK("", v, h), HSTACK(u, m, BYROW(m, SUM)), HSTACK(h, BYCOL(m, SUM), SUM(m)))
)
apart of cosmetics main values results are 2 simple MAP constructions
MAP(u, LAMBDA(x, SUM((x = t) * a)))
and,
MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))),
Excellent solution Mike, as always. Thank you!!!
You are welcome!!!
Thank you Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!!!
Nice formula tricks. Thank you Mike :)
Nice useful trick! I just added it to my Excel toolbox.
Yes!!!! The more tools in that toolbox, the more fun : ) : )
Thank you Mike, appreciate tip to find your conditional format range.
I really must explore the "go to" functionality.
Go To is one of those old school features that is so very useful!!!
Good. Several ways to get the helper column. Several ways to summarize the data. In this situarion I prefer ROUNDUP to fill the helper and GROUPBY/PIVOTBY for the summarization. My general Excel "rule": formulas over features; Tables over ranges; simplicity over complexity; keyboards over mice.
Thanks for the simplicity, Word Poet Richard Hay : ) : ) : ) : ) : )
Always exploring your mind is ...sir...🙏
Glad to explore with you!!!!!
Amazing video as always Mike. My favorite channel on youtube :)
Thank you!!! It is great to hang out with you, Nader : ) : )
@@excelisfun likewise Mike :) :) :) i learn a lot from u
I'm sure you already know this, but watching your video inspired me to try using the spill operator in the conditional formatting "Applies to" and it "worked". But sadly when going back into edit the rule, Excel has replaced the dynamic spill with the hardcoded address :(
Perhaps MS could fix this in the future to help us having to stop guessing when cells to apply the CF to.
It would be good if MS did that...
Love it Mike! Great video thanks!
You are welcome, Teammate Chris M!!!
Thanks for the excellent videos. Has anyone told you that you are a voice talent too? You have the exact same voice as Ross Geller from friends. I can imagine having my answering machine with a personalized message in your voice! $$$$ 😀
To funny... lol
Great video MIKE thanks! ... ...I am still waiting for MS .... to release these features in the stable version ...
I have a question .... recently a parameter was added that I don't understand to both functions .... do you know anything about its use?
Yes, the new feature allows you to have report as it is now, or in table format. You can try it: put two conditions in rows and try both parameters : )
Yes, MS is taking longer to release these out of Beta than any other feature ever.
Very very useful video thanks
You are welcome!!!
Great video! When those two will be available outside of the business insider beta channel?
Well... We have no idea. Why? Because usually the beta appears in M 365 in a few months. BUT: these two functions have already been over a year!!!! I think that there were some programming issues and so it is the new world record for longest beta period ever. But, when then get it right: WOW!!! It will be a game changer : )
Just awesome ❤❤❤❤
Cool: Just fun!!!!
Thanks Mike!!! :) :)
You are welcome, Formula Guy John!!!!
Nice trick, but i wonder what is going on because I know GROUPBY and PIVOTBY even lambda are python function(pandas) and they also add python in excel, is it start of excel to derive to python language? 🤔🤔
I do not know... : (
How can we perform wraprows and wrapcolumns in power query or power bi?
As far as I know, there is not an easy way like in the worksheet. Maybe someone else knows? Otherwise, I will try a few things and see if I can figure it out : )
When is it going to be available for the general public though?
Sigh.
Well... We have no idea. Why? Because usually the beta appears in M 365 in a few months. BUT: these two functions have already been over a year!!!! I think that there were some programming issues and so it is the new world record for longest beta period ever. But, when then get it right: WOW!!! It will be a game changer : )
How can we make a progress bar in a cell without disturbing the actual data present in it ?
I am sorry, I do not know. : (
👁 👍 😅 👌
Thanks, Teammate!!!!
@@excelisfun thank you!
Excellent....thanks...
You are welcome for the EXCELlence!!!
Thanks Mike for a great video.👍
While waiting for the GROUPBY and PIVOTBY functions, we can do this task using functions available to everyone (Excel 365).
For GROUPBY it would be something like this
=LET(
dates, InvoiceSales35[Date],
values, InvoiceSales35[InvoiceAmount],
year_half, YEAR(dates) &", " & IF(MONTH(dates)
Beautiful, PQ Poet and Array Formula Master Bill Szysz!!!! : ) : )
Super cool , or these:
=LET(
d, InvoiceSales35[Date],
a, InvoiceSales35[InvoiceAmount],
t, YEAR(d) & ", " & IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"),
u, UNIQUE(SORT(t)),
m, MAP(u, LAMBDA(x, SUM((x = t) * a))),
VSTACK({"1/2 Year", "Sum of InvoiceAmount"}, HSTACK(u, m), HSTACK("Total", SUM(m)))
)
=LET(
h, "Total",
d, InvoiceSales35[Date],
a, InvoiceSales35[InvoiceAmount],
y, YEAR(d),
t, IF(MONTH(d) > 6, "Jul 01 - Dec 31", "Jan 01 - Jun 30"),
u, UNIQUE(SORT(y)),
v, TOROW(UNIQUE(SORT(t))),
m, MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))),
VSTACK(HSTACK("", v, h), HSTACK(u, m, BYROW(m, SUM)), HSTACK(h, BYCOL(m, SUM), SUM(m)))
)
apart of cosmetics main values results are 2 simple MAP constructions
MAP(u, LAMBDA(x, SUM((x = t) * a)))
and,
MAP(IFNA(u, v), IFNA(v, u), LAMBDA(i, j, SUM((i = y) * (j = t) * a))),
@@Excelambda , Thanks for the old school magic, Excel Lambda!!!!!