Numerous programming languages have a map() function, and they all do the same thing. The Map() function applies a function to each element and returns a vector of the same size. What does that mean? It means you can use the Map() function, which will apply a calculation to a cell and cascade down to all continuous cells.
Let's say we have a table (tbl_EE) that looks like this:
It's just a data dump from our HRIS for executive rewards. I converted it to a table and named it "tbl_EE". It's a simple table that lists the employee name, salary, short-term incentive percentage, long-term incentive percentage, and department. Most executive compensation professionals download more information, but this is a simple example.
We can do something like this:
We don't want to touch the original table (you never do that). You always want to pull data from the table and do the calculations. In this case, we want to see a proposed salary if we increase everyone's salary by 7%. Just create your headers (row 16), pull the Name and Salary,
=tbl_EE[[Name]:[Salary]]and create a Proposed Salary column.
We can use the Map() function to do that with an entry into one cell (D17).
=MAP(tbl_EE[Salary], LAMBDA(param1, param1*1.07))The Map() function is one of Excel's new Lambda() functions. A Lambda() function is a custom function you can wrap within another function. You can reuse this function anywhere in your workbook (I'll discuss that in a future blog). Functions that you can use Lambda() with are Map(), Scan(), ByCol(), ByRow(), Reduce(), IsOmitted(), and MakeArray(). But let's stick with Map() today.
Our Map() function says to use the Salary field in the table tbl_EE. The Lambda piece says to use the Salary field as a parameter. I named it "param1" in this case, but you can name it anything. (I recommend that you don't use spaces.) Then, with this parameter "param1," apply 7%. You only need to enter this in cell D17, and it will cascade down all the continuous rows. Pretty sweet.
Let's get a bit more complicated. Let's say we want to give all employees in R&D a 10% raise and everyone else a 5% raise. Take the entire table as you see below:
We must apply a Map() using two fields, the Salary and Dept. We need two parameters for our Lambda() function in this case. We could name the parameters "param1" and "param2", but I wanted to show you that we could name them anything. In this case, we named them "salary" and "dept".
Since we are using two parameters, we also need to specify that in our Map() function.
=MAP(C28:C33,F28:F33,LAMBDA(salary,dept,salary*IF(dept="R&D",1.1,1.05)))Notice that we can use the If() function within Lambda() to indicate the R&D vs the non-R&D departments/raises.
Pretty cool, huh?
In the next example, we'll calculate the short-term incentive dollar opportunity, the long-term dollar opportunity, and the total compensation. Given what we just reviewed, you can guess we need two parameters for STI$ and LTI$. And we need three parameters for Total Comp.
The STI$ is calculated by taking Salary * (1+STI) or
=MAP($C$40:$C$45,D40:D45, LAMBDA(param1,param2, param1*param2))The same with the LTI$. Take Salary * (1+LTI).
=MAP($C$40:$C$45,E40:E45, LAMBDA(param1,param2, param1*param2))The Total Comp is calculated by adding Salary + STI$ + LTI$.
=MAP($C$40:$C$45,G40#, H40#,LAMBDA(param1,param2,param3, param1+param2+param3))Once you have that, you can sort it:
You can also filter it:
I hope this simple introduction will kickstart your thinking about how you can use the Map() function in your daily workflow.
I may do more Lambda() function articles in the future.
Here is the file for you to use.
P.S. You can find some interesting (and complicated) Lambda() functions on the internet. Here are several. Here is another one that replaces unwanted characters. Very nice!
