With the new LET function in Excel, you can define values and use them later in a calculation. If you know how, you can also use it to document the logic of your formulas within the cell in a much nicer way than could be done before.
This video walks through creating, documenting, and reviewing a complex formula using LET, based on a question from the FMWC in 2021. The question is about modeling a wealthy individual's net worth, combining a business interest, various real estate holdings and mortgages, a couple of cars, and a stock portfolio - the video also talks about extracting stock prices using the new STOCKHISTORY function.
If you want to try it yourself, you can buy the case materials (including the questions, instructions, Excel file with assumptions, and a worked solution) for $10 from the FMWC here:
https://www.fmworldcup.com/product/stage-5-2021-whats-my-net-worth/
Better yet, you can still get a ticket for this weekend's round here:
https://www.fmworldcup.com/product-category/regular-competition/
(To be clear, I don't get commission or anything like that for referring you, and you don't need to buy the case to watch the video)
Sections
00:00 Introduction
04:31 Why use LET?
09:00 Worked example - mortgage balance with LET
13:34 Auditing a complex LET formula
15:17 Asset valuations
17:40 Stock prices with STOCKHISTORY
24:09 In-cell documentation with N()