Every time I watch one of your videos I'm amazed at the power of Excel and at your brilliance as an Excel(lent) user! Truely inspiring videos. Thank you for all the effort you put into making these videos.
Years spent creating "if greater or equal to one date AND less than or equal to another date" formulas... solved with some creative thinking to use the median function. Sweet. Putting that in my toolbox. 😀
Great video!! A single cell formula for entire result array, 3 rows: dates sequence, total days, cumulative: =LET(sd,Table1[Start Date],ed,Table1[End Date], m,MIN(sd),x,MAX(ed), s,SEQUENCE(,x-m+1,m), a,(sd=s), b,BYCOL(a,LAMBDA(x,SUM(x))), t,SCAN(,b,LAMBDA(v,i,v+i)), VSTACK(s,b,t) )
Same formula without the new functions: =LET(sd,Table1[Start Date],ed,Table1[End Date], m,MIN(sd),x,MAX(ed),r,ROWS(sd), c,x-m+1,q,SEQUENCE(,c),s,q+m-1, a,(sd=s), b,MMULT(SEQUENCE(,r)^0,a), t,MMULT(b,--(SEQUENCE(c)
Great work Mynda! I'm sure there are different ways of doing this. For the formula, so they spill, you could experiment with using the new BYCOL, BYROW, VSTACK, SCAN, AND LAMBDA functions (e.g. for the cumulative sum formula). Also, for if a date falls within two dates, you could use this, so it still spills as an array, of 1s and 0s (no need to drag the formula to the right): =LET( start_date,$C3,end_date,$D3, date_heading_array, F2#, date_matrix, (date_heading_array >= start_date) * (date_heading_array
@@MyOnlineTrainingHub My LET formula used really long before the new March functions were introduced I spent quite some time thinking about how I could avoid using Power Query using array functions, so all my tables would automatically and instantly update, without needing to run a workbook refresh E.g. my initial attempt here (warning, very long 'LET' formulae) >> docs.google.com/spreadsheets/d/1GCZ90nC-l8qlZrcyUfic-Shj0RcAE5xd/edit?usp=sharing&ouid=101105745586847596523&rtpof=true&sd=true With the new, new functions, though, its now become possible to do some really great things, in half as many lines of formula writing. Something I'm super grateful for! : )
Hello Mynda, Thank you for the great tutorial on creating a Planned S-curve, The last part of the tutorial on Actual Cumulative Actual went so fast I did not get to understand the process, Do you have a tutorial for that part? Much appreciate
I don't have a separate tutorial, but if you download the example file from the link in the video description you can see how the data relates to the chart. If you get stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Great dear Mynda, greetings from Freiburg/Germany near Swiss Border. As an ecomist I believe in many occurrencies in nature and in economics following a s-curve. . By the Way in my works for my doctor degree, I found out that the use of energy in relation to the GNP follows during the development of countries economy an s-curve. Do you have an idea to use multidimensional data to show multidimensional s-curves similar a mountain or so? best wishes Wilhelm
Is it possible to create a timer in excel so after a certain amount of time(like seconds or minutes), if you have an image in a cell, the image changes to a different image after a set amount of time using only formulas(no VBA)? If possible the timer would be out of sight and you would only see the change in image.
Every time I watch one of your videos I'm amazed at the power of Excel and at your brilliance as an Excel(lent) user! Truely inspiring videos. Thank you for all the effort you put into making these videos.
Wow, thank you for your kind words, Russell!
A long overdue "Thank you" for all the great videos and projects you post. THANKS and great job!
Very kind of you 🙏😊
Very brilliant use of the MEDIAN formula. Thank you very much Mynda!!!
Glad you liked it, Ivan!
Years spent creating "if greater or equal to one date AND less than or equal to another date" formulas... solved with some creative thinking to use the median function. Sweet. Putting that in my toolbox. 😀
Great video!!
A single cell formula for entire result array, 3 rows: dates sequence, total days, cumulative:
=LET(sd,Table1[Start Date],ed,Table1[End Date],
m,MIN(sd),x,MAX(ed),
s,SEQUENCE(,x-m+1,m),
a,(sd=s),
b,BYCOL(a,LAMBDA(x,SUM(x))),
t,SCAN(,b,LAMBDA(v,i,v+i)),
VSTACK(s,b,t)
)
Same formula without the new functions:
=LET(sd,Table1[Start Date],ed,Table1[End Date],
m,MIN(sd),x,MAX(ed),r,ROWS(sd),
c,x-m+1,q,SEQUENCE(,c),s,q+m-1,
a,(sd=s),
b,MMULT(SEQUENCE(,r)^0,a),
t,MMULT(b,--(SEQUENCE(c)
Thanks for sharing!
Great work Mynda!
I'm sure there are different ways of doing this. For the formula, so they spill, you could experiment with using the new BYCOL, BYROW, VSTACK, SCAN, AND LAMBDA functions (e.g. for the cumulative sum formula). Also, for if a date falls within two dates, you could use this, so it still spills as an array, of 1s and 0s (no need to drag the formula to the right):
=LET(
start_date,$C3,end_date,$D3, date_heading_array, F2#,
date_matrix, (date_heading_array >= start_date) * (date_heading_array
Cheers, Sachin! Indeed, you can use the new functions. Most people don't have them yet, so I went with something everyone can use.
@@MyOnlineTrainingHub My LET formula used really long before the new March functions were introduced
I spent quite some time thinking about how I could avoid using Power Query using array functions, so all my tables would automatically and instantly update, without needing to run a workbook refresh
E.g. my initial attempt here (warning, very long 'LET' formulae) >>
docs.google.com/spreadsheets/d/1GCZ90nC-l8qlZrcyUfic-Shj0RcAE5xd/edit?usp=sharing&ouid=101105745586847596523&rtpof=true&sd=true
With the new, new functions, though, its now become possible to do some really great things, in half as many lines of formula writing. Something I'm super grateful for! : )
Very much helpful Maam
More than I expected Mynda. Great Video
Great to hear!
Thaaaaaaaaaaaaaaank you for that! It is what I was looking for a long time!
So pleased I could help, Thiago!
Thanks Mynda, this was another great video!
Cheers, Chris!
Hi Mynda!Really Great Example...Thank You :)
Thanks so much, Darryl!
This is great, just in time as I am trying to make somthing similar in power bi, BTW can you make a video how to do a project Gantt chart in power bi
Hi Ahmed, you can probably get a Power BI custom visual for your Gantt chart.
Thank you for sharing your expertise on the tool. Such a great help! 👍🏻
Glad you enjoyed it!
Amazing video , you are the best
Thanks for your kind words!
Hello Mynda, Thank you for the great tutorial on creating a Planned S-curve, The last part of the tutorial on Actual Cumulative Actual went so fast I did not get to understand the process, Do you have a tutorial for that part? Much appreciate
I don't have a separate tutorial, but if you download the example file from the link in the video description you can see how the data relates to the chart. If you get stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Love this. I do not see a link to the median video though.
Oops, here it is: th-cam.com/video/o6W-AbpLeqI/w-d-xo.html
Thank you for this amazing share.
Glad you liked it, Syed!
Great dear Mynda, greetings from Freiburg/Germany near Swiss Border. As an ecomist I believe in many occurrencies in nature and in economics following a s-curve. . By the Way in my works for my doctor degree, I found out that the use of energy in relation to the GNP follows during the development of countries economy an s-curve. Do you have an idea to use multidimensional data to show multidimensional s-curves similar a mountain or so? best wishes Wilhelm
Hello! You could add more dimensions in the form of more series (lines).
Amazing!!!! Thank a lot!
Glad you like it!
Thank you.
You're welcome!
Thanks for sharing this mam
My pleasure!
Thanks a Lots!....
You're welcome!
Always useful
Great to hear, Marco!
Is it possible to create a timer in excel so after a certain amount of time(like seconds or minutes), if you have an image in a cell, the image changes to a different image after a set amount of time using only formulas(no VBA)? If possible the timer would be out of sight and you would only see the change in image.
Not possible with formulas. You'd have to use VBA.
Can you help me out if my project is more than 12 months.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
is there a way to do this with slicers but still in excel?
Slicers just filter data, so yes, you could filter the chart inputs with slicers.
When clicking my dates they appear as the cell I am clicking and not "Tabel1end date". What am I doing wrong?
I have my data formatted in an Excel Table: th-cam.com/video/Du73CPqWGQw/w-d-xo.html
Okay cool,
But I wonder if this can't be done with Power Query.
I'm going to say no, because you should be returning tabular data from Power Query and this requires Pivoted data.