Calculate Total FTE for each manager in a hierarchy

stevedjswitch

New Member
Joined
May 20, 2015
Messages
10
Apologies if this is a duplicate, but I couldn't find this exact question asked elsewhere;

I have a worksheet which shows standard organisational hierarchy, i.e. a column of employees and a column with their unique IDs, plus a column which shows their managers' ID (each manager ID is also an employee).

I also have a column which shows for each employee their employment percentage or FTE (full time equivalent) - i.e. full time = 100%

What I'd like to do is display the total sum of FTE's that each managers has within their control, both directly reporting to them, and indirectly, through people who report to them, who also manage people. There's no max to the number of potential layers.

I can get to a count of employees per manager through a UDF which sets up a recursive count, but something similar which will carry out a sum function is eluding me.

I guess another UDF would probably be the best option, but if there's a non-VBA version that works better, I'd be keen to hear it.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

slinky

Active Member
Joined
Dec 19, 2008
Messages
294
I think the limitless depth is the issue from a standard formula perspective.
Unless you could use a helper cell to give you an ultimate manager, or an array, as you'd want to know at each level, I think you're going to struggle doing it "in sheet".
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
employeeidlevelFTEM12M13
id1M12175percents435735
id2M1316519 EMPLOYEESemployees710
id3M13190FTE4.357.35
id4M1100BIG BOSS = M1
id5M12140
id6M121502 DIRECT REPORTS
id7M13175
id8M13175M12M13
id9M12100id9id13M1
id10M12175
id11M13150M12M13
id12M13150directdirect1.001.00
id13M13100reportsreports4.357.35
id14M121355.358.35
id15M12160id1id2
id16M13180id5id3
id17M13180id6id713.70
id18M121100id10id8
id19M131100id14id11
id20M13170id15id12
id18id16
id17
id19
id20
is this the sort of thing - will take an hour or so to set up but then it is done

<colgroup><col><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>
 

stevedjswitch

New Member
Joined
May 20, 2015
Messages
10

ADVERTISEMENT

Hmmm. I think you may be right regarding standard formula.

I think the answer must be a user defined function, similar to one I found to get the total headcount, but I can't make my stupid brain think of it!
 

viktiw

New Member
Joined
Jan 3, 2015
Messages
49
Hi steve,
i believe you have a data as shown below
Employee Name</SPAN>Employee Id</SPAN>Manager's Name</SPAN>Manager's ID</SPAN>FTE %</SPAN>
Mark</SPAN>11</SPAN>Harry</SPAN>21</SPAN>90%</SPAN>
Tom</SPAN>12</SPAN>Morgan</SPAN>22</SPAN>100%</SPAN>
Jim</SPAN>13</SPAN>Morgan</SPAN>22</SPAN>80%</SPAN>
David</SPAN>14</SPAN>Harry</SPAN>21</SPAN>40%</SPAN>
Vik</SPAN>15</SPAN>Harry</SPAN>21</SPAN>50%</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>


And i belive you are looking for number of employees working under Mr. Harry and Mr. Morgan, right ?

A simple pivot table can give you these details and others.
Create a pivot table with Manager's name as Row Field and Employee Id/Name as data field and set it to Count of Employee id/Name.

It will give you that there are 3 Employees working under Mr. Harry and 2 Employees working under Mr. Morgan.

Hope i have understood your question correctly.
Let me know if i didn't.

Thanks,
vik
 

stevedjswitch

New Member
Joined
May 20, 2015
Messages
10

ADVERTISEMENT

Hi Vik

That's exactly how my data would be structured, but not quite what I'm looking for as a solution. In your example above, let's assume that there's a line added in which Mr Morgan also reports to Mr Harry. What I'm looking for is a method to calculate the total sum of the FTE reporting to each manager. So the answer for Mr Harry would be the total sum of the FTE all his direct reports plus the total sum of FTE of people who report to Mr Morgan.

This is a simplified way of explaining it, as in reality, some of those people who report to Mr Morgan, may also be supervisors themselves, so the complexity is in effectively recreating an org hierarchy on the fly and then counting or summing down from each level to the bottom.
Hi steve,
i believe you have a data as shown below
Employee Name</SPAN>Employee Id</SPAN>Manager's Name</SPAN>Manager's ID</SPAN>FTE %</SPAN>
Mark</SPAN>11</SPAN>Harry</SPAN>21</SPAN>90%</SPAN>
Tom</SPAN>12</SPAN>Morgan</SPAN>22</SPAN>100%</SPAN>
Jim</SPAN>13</SPAN>Morgan</SPAN>22</SPAN>80%</SPAN>
David</SPAN>14</SPAN>Harry</SPAN>21</SPAN>40%</SPAN>
Vik</SPAN>15</SPAN>Harry</SPAN>21</SPAN>50%</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>


And i belive you are looking for number of employees working under Mr. Harry and Mr. Morgan, right ?

A simple pivot table can give you these details and others.
Create a pivot table with Manager's name as Row Field and Employee Id/Name as data field and set it to Count of Employee id/Name.

It will give you that there are 3 Employees working under Mr. Harry and 2 Employees working under Mr. Morgan.

Hope i have understood your question correctly.
Let me know if i didn't.

Thanks,
vik
 

stevedjswitch

New Member
Joined
May 20, 2015
Messages
10
Hi oldbrewer. Thanks for answering, however I'm not totally sure I understand your solution. Are you suggesting using helper tables to pull out and summarise the direct reports for each supervisor?
employeeidlevelFTEM12M13
id1M12175percents435735
id2M1316519 EMPLOYEESemployees710
id3M13190FTE4.357.35
id4M1100BIG BOSS = M1
id5M12140
id6M121502 DIRECT REPORTS
id7M13175
id8M13175M12M13
id9M12100id9id13M1
id10M12175
id11M13150M12M13
id12M13150directdirect1.001.00
id13M13100reportsreports4.357.35
id14M121355.358.35
id15M12160id1id2
id16M13180id5id3
id17M13180id6id713.70
id18M121100id10id8
id19M131100id14id11
id20M13170id15id12
id18id16
id17
id19
id20
is this the sort of thing - will take an hour or so to set up but then it is done

<colgroup><col><col span="8"><col><col span="3"></colgroup><tbody>
</tbody>
 

stevedjswitch

New Member
Joined
May 20, 2015
Messages
10
OK, this is an example of the output I'm after. The Headcount Managed is calculated using a UDF which uses a recursive count, but I can't get my head around how to calculate the FTE Managed.

Excel 2010
ABCDEFG
1Employee NameEmployee IdFTE %Manager's NameManager's IDHeadcount ManagedFTE Managed
2Mark1190%Morgan213225%
3Tom12100%Morgan2200
4Jim1380%Morgan2200
5David1440%Harry2100
6Vik1550%Harry2100
7Morgan22100%Harry216495%
8Harry21100%9685%
9Crystal1650%Mark1100
10Brian4575%Mark1100
11Helen23100%Mark1100
12
13Total Count = Total FTE Sum =
1410785%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top