How can i calculate over time hours keeping in view the Sundays, if they work on holiday they are paid full hrs as overtime and rest days above 8 hrs is over time.

Fascinating. The only problem I find with LAMBDA and its help functions is how they make the formula almost unrecognisable from the original formula. My workaround, insert a couple of extra columns and break the formula down into parts to see how it changes. I also added some =FORMULATEXT cells to see at a glance how the formula changes. It works for me. Obviously, I downloaded your "...Complete" file and left it untouched to compare my WIP in the "...Start" file with the finished product. I'll need some practice.

Innovative user-defined function for creating impactful and dynamic chart titles. This UDF revolutionises data visualisation.

So well explained! Thank you!

Amazing tutorial and cool result! Thanks! I had to handle some formulas a bit differently to work with my Excel 2019 where FILTER and XLOOKUP do not exist, but got to the same result thanks to your tut and UDF function.

More Excel magic. Liked and subscribed. Thanks for your time and effort.

Thanks for tutorial sir Please answer my question sir What happened if I just rename the data source? Is there changing the data and all the stepped I run in my power query?

If we need to add the new table dynamic what we can do to proceed with append

Wow! So many choices, so little time.🤠 Personally, my favourite was the Direction...but with a little modification. I included the currency sign and added a space between the arrow and the £. I found it looks cleaner. Thanks for your time and effort. Now to go back to your "Change number format based on cell value" video.

Nice video, though many companies do not allow their employees using macros or vba due to high security standards. I always struggle to share such files and therefore my first attempt would be a hack here with overlaying text boxes which are already in a certain color. (I haven’t tried this idea so far)

How can I convert this to one row with 12 columns that show the progress of the months in a year?

new to me.🙂

Apotheosis👏👏👏👏👏

You asked in your email if I have ever seen that technique before and the answer is yes, I have been using it for chart titles and in the same way you did in a cell for several years now.

Brilliant! I've been using Excel since 1993 (after using VisiCalc and of course Lotus 123 for years). I have never seen anything like this in any Spreadsheet program. I'm not a fan of VBA, but this is one time it's well worth it. Thanks!

Hello! i am wondering something. For the second formula that gets the prior to value. What value would be returned if Q1 is selected as value in the quarter drop down list ? Q0? Shouldn’t we insert if formula with conditions to calcul quarter number ?

Brilliant!! Thanks.

I like how you're always looking at your excel. 😊

Thank you, Mark. I've been considering an alternative method without using VBA by placing each element in separate cells, formatting them accordingly, and then creating a linked picture from those cells for the chart title.

Very helpful, thank you. The reason for using power query is to automate this reconciliation task. It would be great if you could show what happens when you get different lists next month and how to update the query and get the list of items not matching in a few clicks. Regards

No I haven't seen it ever before. Thanks for the share.

Thank you

dynamic titles, check, but have them formatted, that's new. But as I'm probably not allowed to make xlsm-files anymore as they don't play well with Sharepoint, I can't use VBA anymore . 🤔

Great as always Mark. Thanks

IfindExcelformulaseasiertoreadwhenspacesareused... 🤔

Wonderful video….now can you write a macro to make the users appreciate the work that went into this olution😊

Awesome Mark, great video!

Awesome! 👍👍

spent 15 minutes on this. doesnt work at all. what is this data thing? where does it come from? why does A1:C1000 not work? waste of time

Before Lambdas, we could define a Name, let's call it Eval, with a Refers To formula of =EVALUATE(G20), and in another cell simply use =Eval to display the result. But this means we can only use it to evaluate the formula in G20 (which is hard-coded in the Refers To formula), so it's not as flexible as the Lambda. Maybe we should call this Name EvalG20, to distinguish it from other Evals we may need to define.

Nice, I'm surprised there's not already a built in function like this and now I'm wondering what other VBA can be run inside of a LAMBDA Function

What if my data source is a web link? Will it work then?

One button for them all..."my precious". Great video. Thanks for your time and effort.

Good way of picking my lottery numbers. Sadly, still only a 1 in 139,838,160 chance of winning the Euro Millions. 😒 Just need it to work for numbers, letters and special characters now for generating random passwords.

Great video - thanks for that. I'm not able to visit your website because of an certificate exception.

You can use if else or best switch statement

Usefull trick. Thank you for share

Excelent tip. Thank you for share it

Does anyone know why the ALT method is not working for me ? I am using excel 2020. When i click to open with ALT pressed, it opens in the same instance, without any pop-up.

Is there an option for dynamic print area?

Really good man. I very much liked the background information about implicit intersection. I now finally understand the control shift enter thing, never really knew what that was!

That's so cool

Sir, your tutorial has helped me in many different ways. But I want to know one thing that when we are writing the formulas in the cell then those formulas are visible or going in the formula bar. I want to write that formula(s) by VBA to solve the problem. How is it possible? If you give a detailed guide, it will be very helpful. For example, I am doing Date of Retirement calculation with EOMONTH or I am writing a specific formula in a cell to calculate Present Age. I want to run these formula in VBA. How to do? That means, I will write Date of Birth in one cell and Present Age in a cell and Retirement Date will be written in another cell simultaneously/automatically.

Make downloading file easy, it's very difficult requires first sign up then another step

Very nice

Ifs there any situation when the fxTextToFormulaUDF would return #VALUE! with your "1+1" example

I've been searching to do this for weeks. Thanks a lot!!!!!!!!!!!!!!!!!!!!!

Any option that array functions could work inside the table as they throw #spill error

Hi Mark what if you have different dates

I’m a sucker for a good Lambda formula! Cool trick for making it volatile. Loved the editing too, pro quality! What software do you use?