![[Bloomberg] Nintendo Switch 2 Set for Gaming’s Biggest Ever Launch Even at $400-Plus](/forums/data/news_thumbnails/0/90.jpg?1741939443)
- Thread starter
- #1
Chapter 1: Learn badly with me, ggx2ac! - chapter 1: build a relational database using SQLite
I said this in chapter 1:
In relation to that, I learned now that Microsoft Excel (back in 2021) and also Google Sheets are now programming languages because they can now use Lambda functions and recursion hence, they are considered Turing-complete. Source: https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/
I don't use Excel/Sheets but as mentioned in my chapter 1 quote above, it's obvious that most people here use those programs. It is possible that you do not use it in a work-context, so it is possible that you have been stuck with the same Excel knowledge since the year 2000.
What is functional programming? It is a programming paradigm that allows you to write programs in a particular style, that then gets translated to machine code for a computer to run your program. There are many programming paradigms: Imperative programming, declarative programming, object-oriented programming, functional programming, reactive programming and so on. Each programming paradigms has trade-offs, so each paradigm is good at doing a particular thing so think of it as a set of tools needed to do a job.
What is a lambda function? Lambda functions are derived from Lambda calculus (you won't need to learn those details).
What is a function? Here is a simple illustration in JavaScript. A function takes an input parameter (of which you can have many parameters), it does a computation using that input and then returns an output.
Here is an example of a lambda function using JavaScript to illustrate the same thing: (lambda in this context is just the name of the variable assigned using const, lambda is not a keyword. Output is illustrative and is not the name to return something.)
To show a working example:
Lambda functions are more of an expression rather than a statement compared to regular functions in some programming languages, as you can see above, I did not need to declare the words function and return but it still did the same things as a regular function, it took an input parameter and returned an output.
Here's another example using two input parameters:
Lastly, I want to show an example of a lambda function in JavaScript using recursion:
Compared to the other lambda function, this required the use of curly brackets to create a block of code because I wanted to make this readable so it actually reads more like a statement than an expression because there is now an if else statement there and return statements.
It is possible to turn this into an expression, it would just make things harder for a beginner to understand, here is the expression version:
This version works exactly the same as the one above it but is now concise and in one line. They both use recursion where recursion is the act of a function calling itself in the middle of a computation until it reaches an exit condition to finish that computation.
To then call either of those subtractAll functions I would type:
The input parameters are: list (list of values you want to subtract), index (the index of the list which begins at 0), accumulator (a value which we want to hold onto as we keep calling the function recursively and we have to set an initial value of which we set it to 0). When the function is called, it checks the exit condition where if the next value in the list does not exist, then it will finish computing by returning the accumulator with the last value in the list. If the list hasn't been completed, then it will call the function again but with index incremented by +1 and the accumulator (which was set to 0 will now be subtracted by the first value in the list). The function calling repeats doing the same computations until the exit condition is reached. If there was no exit condition the program would likely stop because it would tell you that it has called your function too many times and it has reached the limit of doing that due to the memory (RAM) allocated to that function.
One problem with understanding this function here is that this is JavaScript which uses dynamic typing. For this function to work, you need to be aware that your list parameter has to be of type list: []. A list is a type that can hold many values, it does not have to be numbers, it can be strings as well. If I edited the function to add and not subtract and to change that starting value of the accumulator from 0 to "", it would be possible to use recursion to string a list of letters such as ["h", "e", "l", "l", "o"] and the return value would be "hello". Because JavaScript uses dynamic typing, it does not tell you if you are doing something wrong until you run the program of which you would likely end up with a type error if you did not use a list type for the list parameter.
With those examples using JavaScript completed, let's move onto using lambda functions in Google Sheets. Why am I using Google Sheets? I was originally going to do this in Microsoft Excel on "Excel for web" which is the web version of Excel used in Microsoft 365: https://www.office.com/
That was until I learned that I could not store the lambda functions in the "Name Manager" of Excel for Web because it is not available there. Source: https://support.microsoft.com/en-us...f13ac-53b7-422e-afd2-abd7ff379c64#ID0EBBH=Web
Why is the Name Manager important? It lets you store the lambda function you created so that you can keep calling it elsewhere using its name, that way you don't have to write the whole function over and over again wherever you use it.
Due to that issue, the example of using Lambda functions will be done using Google Sheets. While it is possible for you to either purchase Excel (at a high price) or to use the Microsoft 365 subscription service, I wanted to use something that could be done for free and while the web version of Excel is free, it does not contain the same features as the desktop (Windows, Mac) version of Excel such as the Name Manager mentioned above.
Assuming you have a Google Account, go to: https://docs.google.com/
Then click on the main menu button on the top-left corner and click on sheets and then click on blank.
Let's do an example that's relevant to video game sales data. I will be referencing Capcom's Platinum Titles data: https://www.capcom.co.jp/ir/english/business/million.html
The following is what I entered into Google Sheets, I then copied all the cells and pasted them into the post here which conveniently converted it into an HTML table using BB code so that I don't have to take screenshots to show you what is happening.
The table above is what I have started with in Google Sheets, you will notice the Quarters 1-4 are blank, that is because the numbers given by Capcom are cumulative.
Let's use Lambda functions to fill in the blanks, go to an empty cell outside the above data and type:
We are creating a LAMBDA, its input parameters are current_quarter and last_quarter which are separated by commas, following your parameters is your computation, in this case we are taking the current quarter and subtracting it by the last quarter.
When you press enter, you are likely to get an error. This is because you have created the function, but you have not called it. You can test out the function by doing something like this:
That (4,2) are the inputs given, the cell will then change to 2 which shows that the Lambda function to works. To store the function so that it can be reused, go to menu bar of the program at the top and click on Data then click on Named functions. A bar will appear from the right side of the window, click on add new function.
You will have to do the following, there is probably an easier way to do this, but this is what actually got it to work for me.
First, under Named function details, type subtract_quarter into the Function name field, this will be changed to all caps automatically.
Second, go to the argument placeholders field, you will enter current_quarter and click the arrow to enter it, and then last_quarter and click the arrow for that to enter it.
Finally, go to the Formula definition field and copy and paste the following:
If you have done all that then click next, it will give you a function preview and then click create. Now we can use that lambda function, go to the quarter 1 column and click the empty cell on row 2 and start typing:
And a dropdown menu should appear with SUBTRACT_QUARTER with named function tagged next to it. click it and it will autocomplete the name for you. Next, for the first title in the row that has FY3/2022, click on the cell that for that row in the First Quarter CML column (it has the value 17.3), then press the button on your keyboard to put in a comma, then click on the cell that is the FY Cumulative number for that same title in FY3/2021 (the value is 17.1), then press enter. If you have done this correctly then the cell in Quarter 1 should give the number 0.2. An auto-fill prompt may appear, click the tick and it will auto-fill the rest of the column. This is what your table should look like:
Note, the first quarter for FY3/2020 isn't 13.1 because MHW released two years earlier, but this will have to do because it is as far back as we are going for this example.
Now we repeat using the same SUBTRACT_QUARTER lambda function for the other quarters (hint: quarter 2 = first half - first quarter CML, quarter 3 = first three quarters - first half, etc) and this is what the completed table should look like:
You will notice this looks similar to the tables on my webpage: https://r134x7.github.io/nintendo-earnings-data-and-other-video-game-companies/#/capcom
Let's finish making it similar to that by doing the YoY% calculations.
Go to Data then Named Functions again, then add new function. Function name is year_on_year_by_quarter, the formula definition is:
Create a column called Q1 YoY which is going to be to the left of Quarter 2. Then use the year_on_year_by_quarter function and click on the quarter 1 cell for FY3/2022, then press the comma button on your keyboard and then click on the quarter 1 cell for FY3/2021. The Q1 YoY value for FY3/2022 should say 0.5.
I have went added two new columns to get started, this is what the table looks like:
I have not auto filled it all the way down because I know the YoY numbers for Quarter 1 of FY3/2021 and FY3/2020 will not be accurate but from Quarter 2 onwards you are at least able to get the YoY numbers for FY3/2021.
I have noticed that the numbers are not formatted similarly to my tables on my webpage. To fix the percentages, click on the column of that percentage e.g., Q1 YoY and then click on Format at the menu bar, then hover the cursor on Number, then click on percent. That's one type of column done. Next, to format the unit sales numbers as Million units, go to Format, then Number, then click on custom number format. Then in the custom number format field, type:
Once you've completed every column, your table should look like this:
Is the table completed? No. If you look at the Q4 CML YoY column, you will notice the cell at FY3/2022 is not -35.71% like on my webpage. How do we fix this? We have to create a lambda function for calculating the Year on Year percentages correctly for the cumulative values.
Once more, Data -> Named Functions -> Add new function:
Function name: YEAR_ON_YEAR_BY_CML
Formula definition:
Click next and create. Now apply your newest function onto the QX CML YoY columns by removing the function that was used in those cells and replacing it with the new one.
Your table should now look like this:
As you can see for the QX CML YoY columns, I couldn't do the FY3/2021 row, because I need the values for FY3/2019 to do it correctly.
The functions for the table are complete and as you can see, it is similar to the tables from my webpage.
Lastly, what was the point of learning Lambda functions?
It is possible that the functions you need are already built-in on Excel/Sheets but when there is something you need to calculate and there is no built-in function for it, it was not so easy to deal with on Excel before 2021. You had to use Visual Basic or JavaScript to create those functions to use is in Excel.
Now that you know how to create Lambda functions in Google Sheets (I can't assume these exact same steps in the example above will work in Excel), you can now figure out how to create your own calculations when there isn't a built-in function for it.
I don't even know if there are built-in functions that are the same as the lambda functions I made in Google Sheets, the point was that I knew how to make them because I already did them before in Typescript for my webpage, those functions are the same used in my webpage in TypeScript, I already knew how to make it.
For some more advanced functional programming methods on Google Sheets, see:
Map function: https://support.google.com/docs/answer/12568985
Reduce function: https://support.google.com/docs/answer/12568597
(There's related functions at the bottom of those pages.)
Chapter end.
I said this in chapter 1:
In relation to that, I learned now that Microsoft Excel (back in 2021) and also Google Sheets are now programming languages because they can now use Lambda functions and recursion hence, they are considered Turing-complete. Source: https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/
I don't use Excel/Sheets but as mentioned in my chapter 1 quote above, it's obvious that most people here use those programs. It is possible that you do not use it in a work-context, so it is possible that you have been stuck with the same Excel knowledge since the year 2000.
What is functional programming? It is a programming paradigm that allows you to write programs in a particular style, that then gets translated to machine code for a computer to run your program. There are many programming paradigms: Imperative programming, declarative programming, object-oriented programming, functional programming, reactive programming and so on. Each programming paradigms has trade-offs, so each paradigm is good at doing a particular thing so think of it as a set of tools needed to do a job.
What is a lambda function? Lambda functions are derived from Lambda calculus (you won't need to learn those details).
What is a function? Here is a simple illustration in JavaScript. A function takes an input parameter (of which you can have many parameters), it does a computation using that input and then returns an output.
JavaScript:
function(input) {
// computations
return output
};Here is an example of a lambda function using JavaScript to illustrate the same thing: (lambda in this context is just the name of the variable assigned using const, lambda is not a keyword. Output is illustrative and is not the name to return something.)
JavaScript:
const lambda = input => output;To show a working example:
JavaScript:
const divideByTwo = x => x/2
divideByTwo(3) // returns 1.5 if this is done in the console of your internet browserLambda functions are more of an expression rather than a statement compared to regular functions in some programming languages, as you can see above, I did not need to declare the words function and return but it still did the same things as a regular function, it took an input parameter and returned an output.
Here's another example using two input parameters:
JavaScript:
const addTwo = (x,y) => x + y
addTwo(3,4) // returns 7 when used in the console of your internet browserLastly, I want to show an example of a lambda function in JavaScript using recursion:
JavaScript:
const subtractAll = (list,index,accumulator) => {
if (list[index+1] === undefined) {
return accumulator - list[index]
} else {
let subtract = accumulator - list[index]
return subtractAll(list,index+1,subtract)
}
};Compared to the other lambda function, this required the use of curly brackets to create a block of code because I wanted to make this readable so it actually reads more like a statement than an expression because there is now an if else statement there and return statements.
It is possible to turn this into an expression, it would just make things harder for a beginner to understand, here is the expression version:
JavaScript:
const subtractAll = (list,index,accumulator) => list[index+1] === undefined ? accumulator - list[index] : subtractAll(list,index+1,accumulator-list[index])This version works exactly the same as the one above it but is now concise and in one line. They both use recursion where recursion is the act of a function calling itself in the middle of a computation until it reaches an exit condition to finish that computation.
To then call either of those subtractAll functions I would type:
JavaScript:
subtractAll([1,2,3],0,0)
// This example returns -6 in the console of your internet browserThe input parameters are: list (list of values you want to subtract), index (the index of the list which begins at 0), accumulator (a value which we want to hold onto as we keep calling the function recursively and we have to set an initial value of which we set it to 0). When the function is called, it checks the exit condition where if the next value in the list does not exist, then it will finish computing by returning the accumulator with the last value in the list. If the list hasn't been completed, then it will call the function again but with index incremented by +1 and the accumulator (which was set to 0 will now be subtracted by the first value in the list). The function calling repeats doing the same computations until the exit condition is reached. If there was no exit condition the program would likely stop because it would tell you that it has called your function too many times and it has reached the limit of doing that due to the memory (RAM) allocated to that function.
One problem with understanding this function here is that this is JavaScript which uses dynamic typing. For this function to work, you need to be aware that your list parameter has to be of type list: []. A list is a type that can hold many values, it does not have to be numbers, it can be strings as well. If I edited the function to add and not subtract and to change that starting value of the accumulator from 0 to "", it would be possible to use recursion to string a list of letters such as ["h", "e", "l", "l", "o"] and the return value would be "hello". Because JavaScript uses dynamic typing, it does not tell you if you are doing something wrong until you run the program of which you would likely end up with a type error if you did not use a list type for the list parameter.
With those examples using JavaScript completed, let's move onto using lambda functions in Google Sheets. Why am I using Google Sheets? I was originally going to do this in Microsoft Excel on "Excel for web" which is the web version of Excel used in Microsoft 365: https://www.office.com/
That was until I learned that I could not store the lambda functions in the "Name Manager" of Excel for Web because it is not available there. Source: https://support.microsoft.com/en-us...f13ac-53b7-422e-afd2-abd7ff379c64#ID0EBBH=Web
Why is the Name Manager important? It lets you store the lambda function you created so that you can keep calling it elsewhere using its name, that way you don't have to write the whole function over and over again wherever you use it.
Due to that issue, the example of using Lambda functions will be done using Google Sheets. While it is possible for you to either purchase Excel (at a high price) or to use the Microsoft 365 subscription service, I wanted to use something that could be done for free and while the web version of Excel is free, it does not contain the same features as the desktop (Windows, Mac) version of Excel such as the Name Manager mentioned above.
Assuming you have a Google Account, go to: https://docs.google.com/
Then click on the main menu button on the top-left corner and click on sheets and then click on blank.
Let's do an example that's relevant to video game sales data. I will be referencing Capcom's Platinum Titles data: https://www.capcom.co.jp/ir/english/business/million.html
The following is what I entered into Google Sheets, I then copied all the cells and pasted them into the post here which conveniently converted it into an HTML table using BB code so that I don't have to take screenshots to show you what is happening.
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | First Quarter CML | First Half | First Three Quarters | FY Cumulative |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 17.3 | 17.5 | 17.8 | 18 | ||||
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 16.1 | 16.4 | 16.8 | 17.1 | ||||
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1 | 14.1 | 14.9 | 15.7 |
The table above is what I have started with in Google Sheets, you will notice the Quarters 1-4 are blank, that is because the numbers given by Capcom are cumulative.
Let's use Lambda functions to fill in the blanks, go to an empty cell outside the above data and type:
We are creating a LAMBDA, its input parameters are current_quarter and last_quarter which are separated by commas, following your parameters is your computation, in this case we are taking the current quarter and subtracting it by the last quarter.
When you press enter, you are likely to get an error. This is because you have created the function, but you have not called it. You can test out the function by doing something like this:
That (4,2) are the inputs given, the cell will then change to 2 which shows that the Lambda function to works. To store the function so that it can be reused, go to menu bar of the program at the top and click on Data then click on Named functions. A bar will appear from the right side of the window, click on add new function.
You will have to do the following, there is probably an easier way to do this, but this is what actually got it to work for me.
First, under Named function details, type subtract_quarter into the Function name field, this will be changed to all caps automatically.
Second, go to the argument placeholders field, you will enter current_quarter and click the arrow to enter it, and then last_quarter and click the arrow for that to enter it.
Finally, go to the Formula definition field and copy and paste the following:
What you will notice is that we have to put in the input parameters twice, once inside the lambda function and then outside it to the right.
If you have done all that then click next, it will give you a function preview and then click create. Now we can use that lambda function, go to the quarter 1 column and click the empty cell on row 2 and start typing:
And a dropdown menu should appear with SUBTRACT_QUARTER with named function tagged next to it. click it and it will autocomplete the name for you. Next, for the first title in the row that has FY3/2022, click on the cell that for that row in the First Quarter CML column (it has the value 17.3), then press the button on your keyboard to put in a comma, then click on the cell that is the FY Cumulative number for that same title in FY3/2021 (the value is 17.1), then press enter. If you have done this correctly then the cell in Quarter 1 should give the number 0.2. An auto-fill prompt may appear, click the tick and it will auto-fill the rest of the column. This is what your table should look like:
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | First Quarter CML | First Half | First Three Quarters | FY Cumulative |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.2 | 17.3 | 17.5 | 17.8 | 18 | |||
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.4 | 16.1 | 16.4 | 16.8 | 17.1 | |||
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1 | 13.1 | 14.1 | 14.9 | 15.7 |
Note, the first quarter for FY3/2020 isn't 13.1 because MHW released two years earlier, but this will have to do because it is as far back as we are going for this example.
Now we repeat using the same SUBTRACT_QUARTER lambda function for the other quarters (hint: quarter 2 = first half - first quarter CML, quarter 3 = first three quarters - first half, etc) and this is what the completed table should look like:
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 4 | First Quarter CML | First Half | First Three Quarters | FY Cumulative |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.2 | 0.2 | 0.3 | 0.2 | 17.3 | 17.5 | 17.8 | 18 |
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.4 | 0.3 | 0.4 | 0.3 | 16.1 | 16.4 | 16.8 | 17.1 |
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1 | 1 | 0.8 | 0.8 | 13.1 | 14.1 | 14.9 | 15.7 |
You will notice this looks similar to the tables on my webpage: https://r134x7.github.io/nintendo-earnings-data-and-other-video-game-companies/#/capcom
Code:
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+
| Monster Hunter: World |
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+
| PS4, Xbox One, PC, DL |
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+
| Jan 2018 | Rank 9 |
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+
| 1st Quarter | 0.2M |
| 2nd Quarter | 0.2M |
| 3rd Quarter | 0.3M |
| 4th Quarter | 0.2M |
+==================================+
| FY3/2022 Cumulative | 0.9M |
| FY3/2022 Cml. YoY% | -35.71% |
| Life-To-Date | 18M |
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+
| (* Excludes shipments of Monster |
| Hunter World: Iceborne Master |
| Edition) |
+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−+Let's finish making it similar to that by doing the YoY% calculations.
Go to Data then Named Functions again, then add new function. Function name is year_on_year_by_quarter, the formula definition is:
and you have to fill in current_year and last_year for the argument placeholders. Click next and create.
Create a column called Q1 YoY which is going to be to the left of Quarter 2. Then use the year_on_year_by_quarter function and click on the quarter 1 cell for FY3/2022, then press the comma button on your keyboard and then click on the quarter 1 cell for FY3/2021. The Q1 YoY value for FY3/2022 should say 0.5.
I have went added two new columns to get started, this is what the table looks like:
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Q1 YoY | Quarter 2 | Q2 YoY | Quarter 3 | Quarter 4 | First Quarter CML | First Half | First Three Quarters | FY Cumulative |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.2 | -0.5 | 0.2 | -0.3333333333 | 0.3 | 0.2 | 17.3 | 17.5 | 17.8 | 18 |
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.4 | 0.3 | -0.7 | 0.4 | 0.3 | 16.1 | 16.4 | 16.8 | 17.1 | |
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1 | 1 | 0.8 | 0.8 | 13.1 | 14.1 | 14.9 | 15.7 |
I have not auto filled it all the way down because I know the YoY numbers for Quarter 1 of FY3/2021 and FY3/2020 will not be accurate but from Quarter 2 onwards you are at least able to get the YoY numbers for FY3/2021.
I have noticed that the numbers are not formatted similarly to my tables on my webpage. To fix the percentages, click on the column of that percentage e.g., Q1 YoY and then click on Format at the menu bar, then hover the cursor on Number, then click on percent. That's one type of column done. Next, to format the unit sales numbers as Million units, go to Format, then Number, then click on custom number format. Then in the custom number format field, type:
And then click apply. Now go to one of the columns with sales units and then click the column, then go to Format -> Number and then click on the custom number format you just created by going down the list and clicking on 0.0"M".
Once you've completed every column, your table should look like this:
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Q1 YoY | Quarter 2 | Q2 YoY | Quarter 3 | Q3 YoY | Quarter 4 | Q4 YoY | First Quarter CML | Q1 CML YoY | First Half | Q2 CML YoY | First Three Quarters | Q3 CML YoY | FY Cumulative | Q4 CML YoY |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.2M | -50.00% | 0.2M | -33.33% | 0.3M | -25.00% | 0.2M | -33.33% | 17.3M | -7.45% | 17.5M | -6.71% | 17.8M | -5.95% | 18.0M | -5.26% |
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.4M | 0.3M | -70.00% | 0.4M | -50.00% | 0.3M | -62.50% | 16.1M | -22.90% | 16.4M | -16.31% | 16.8M | -12.75% | 17.1M | -8.92% | |
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1M | 1.0M | 0.8M | 0.8M | 13.1M | 14.1M | 14.9M | 15.7M |
Is the table completed? No. If you look at the Q4 CML YoY column, you will notice the cell at FY3/2022 is not -35.71% like on my webpage. How do we fix this? We have to create a lambda function for calculating the Year on Year percentages correctly for the cumulative values.
Once more, Data -> Named Functions -> Add new function:
Function name: YEAR_ON_YEAR_BY_CML
Formula definition:
Argument Placeholders: current_year, last_year, year_before_last
Click next and create. Now apply your newest function onto the QX CML YoY columns by removing the function that was used in those cells and replacing it with the new one.
Your table should now look like this:
| Fiscal Year | Title | Release Date | Platforms | Quarter 1 | Q1 YoY | Quarter 2 | Q2 YoY | Quarter 3 | Q3 YoY | Quarter 4 | Q4 YoY | First Quarter CML | Q1 CML YoY | First Half | Q2 CML YoY | First Three Quarters | Q3 CML YoY | FY Cumulative | Q4 CML YoY |
| FY3/2022 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.2M | -50.00% | 0.2M | -33.33% | 0.3M | -25.00% | 0.2M | -33.33% | 17.3M | -60.00% | 17.5M | -52.17% | 17.8M | -47.37% | 18.0M | -35.71% |
| FY3/2021 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 0.4M | 0.3M | -70.00% | 0.4M | -50.00% | 0.3M | -62.50% | 16.1M | 16.4M | 16.8M | 17.1M | |||||
| FY3/2020 | Monster Hunter: World | Jan 2018 | PS4, Xbox One, PC, DL | 13.1M | 1.0M | 0.8M | 0.8M | 13.1M | 14.1M | 14.9M | 15.7M |
As you can see for the QX CML YoY columns, I couldn't do the FY3/2021 row, because I need the values for FY3/2019 to do it correctly.
The functions for the table are complete and as you can see, it is similar to the tables from my webpage.
Lastly, what was the point of learning Lambda functions?
It is possible that the functions you need are already built-in on Excel/Sheets but when there is something you need to calculate and there is no built-in function for it, it was not so easy to deal with on Excel before 2021. You had to use Visual Basic or JavaScript to create those functions to use is in Excel.
Now that you know how to create Lambda functions in Google Sheets (I can't assume these exact same steps in the example above will work in Excel), you can now figure out how to create your own calculations when there isn't a built-in function for it.
I don't even know if there are built-in functions that are the same as the lambda functions I made in Google Sheets, the point was that I knew how to make them because I already did them before in Typescript for my webpage, those functions are the same used in my webpage in TypeScript, I already knew how to make it.
For some more advanced functional programming methods on Google Sheets, see:
Map function: https://support.google.com/docs/answer/12568985
Reduce function: https://support.google.com/docs/answer/12568597
(There's related functions at the bottom of those pages.)
Chapter end.
Last edited: