Assistance with Dividing Accommodation Costs by FTE

[TheWes]

Hi all,

I hope you're doing well.
I’m looking for some assistance with a task related to my current dataset.
The dataset contains around 5,000 rows and includes about 250 unique locations. Please see here a sample.

Sample of dataset

Location	Unit	GP	FTE	Accommodation costs
Roosendaal	RES-123	-	-	5.000
Roosendaal	RES-124	0	1,8	5.000
Amsterdam	0	0	1,6	-
Amsterdam	RES-xxx	-	-	-
Amsterdam	RES-xxx	-	-	-
Amsterdam	0	-	-	-
Breda	RES-xxx	-	-	20.000
Breda	RES-xxx	0	5,2	20.000
Breda	RES-xxx	-	-	20.000
Breda	0	-	-	20.000
Breda	0	-	-	20.000
Breda	0	-	-	20.000
Breda	0	-	-	20.000
Breda	0	-	-	20.000
Breda	0	-	-	20.000
Breda	0	0	0,1	20.000

Problem:
- I have used index match to link the accommodation costs (from another dataset) to all the locations and associated units.
To avoid double counting of costs and to accurately divide the accommodation costs over the units: I want to divide the accommodation costs using the FTE for each 'unique' location.
- So for example in this sample dataset. I want to make a formula which for "Roosendaal, RES-123" will output me 0 and for "Roosendaal, RES-124 will output me 5.000, both in the accommodation column.

Due to the size of the data and reoccurence of steps, manually dividing the costs would be time-consuming. Therefore, I’m hoping you could provide guidance or a potential solution to automate this process.
Any help or suggestions on how to approach this would be greatly appreciated!
Thank you in advance for your support.

Best regards,
W

 

[Vogateer]

Could you show me the explicit calculation you're using to get the numbers for RES-123 and RES-124 here? I don't understand why RES-123 would be 0, and I don't understand how the FTE has been used for RES-124. Do we know that RES-123 and RES-124 are related just by the location, or does the "RES" matter? I feel like a few more examples with explicit calculations could help here.

 

[TheWes]

Thank you for your reply.

A few notes which I hope help:
1) Column 1 through 4 you can consider as hardcoded (location through FTE).
2) Column 5 (accommodation costs) is an index match function which retrieves data from another tab. My problem is that now I allocated the accommodation cost on each location, to all the units. I would like however to divide the accommodation costs over the units based on the FTE of each unique unit. So this would mean that I would allocate 0 accommodation cost to Roosendaal RES-123 and 5.000 to Roosendaal RES-124, since all fte work for Roosendaal (unit) RES-124.
3) RES-123 is zero because there are no FTE working in that unit. Please know, a location can consist of multiple units (RES-123, RES-124 etc.)
4) "Do we know that RES-123 and RES-124 are related just by the location, or does the "RES" matter?" Just by the location, the RES has no meaning.

To your last comment, what other explicit calculations could be helpfull?
I hope this gives more clear information on the topic.

Thank you again.

 

[Vogateer]

So you would want the accommodation costs to show as a percentage of the FTE for the unit. For RES-124 there is 1.8 FTE for the whole location, so 1.8 FTE for RES-124 means 100% of the location's accommodation cost. It looks like Breda would have one RES-XXX with 19,62, and another unit with 0,38. Sound right?

Would you like another column for this info next to Accommodation Costs? If not, I will need to see the other sheet that the Index Match formula is referencing so that I can create a formula that gets info from that sheet.