So my dad asked me for Excel help
Solution 1) Slap together 3 cells that do the intermediary calculations. You can do this in one formula, but prepare for pain-peko. You will mess up, and you won't know where it messed up.
This solution ignores grammar.
Solution 2) If your company pays for Excel 365, you'll have access to the recent(ish) functions LET and LAMBDA. They let you create variables and your own functions, respectively. This makes it easier to chain together intermediary calculations, while being able to test each calculation.
You will have to add the formula in Name Manager. The function is stored in the local workbook, meaning you need to copy it to other workbooks.
I can't show you the actual steps because I refuse to pay a subscription for a spreadsheet program.
This solution ignores grammar.
Solution 3) Open up VBA and write the code.
The function is stored in the local workbook, meaning you need to copy it to other workbooks. You will have to save the workbook as a macro-enabled workbook. Unfortunately, you can't add help for when the user calls the function - they need to know they have to provide the week number. That should be obvious from the function name.
That has potential safety issues because rude people can add malicious code.
This solution deals with proper grammar.
I also shill for Bent Flyvbjerg and Dan Gardner's book "How big things get done".
00:00 Intro, base Excel solution
00:34 Step 1, weeks to whole years
00:59 Step 2, remaining weeks
01:08 Step 3, remaining months
02:30 Excel 365, Lambda and Let
04:43 Testing a Lambda-function
05:50 Lambdas are stored in the workbook
06:54 VBA function
09:24 VBA function, done explaining
10:54 Constructing the string
12:27 Function = OutputString
12:37 Calling the VBA function
13:25 Planning fallacy
Негізгі бет 79 - Function to turn weeks into years and months (Excel, Excel 365 and VBA)
No video
Пікірлер