Hi,
I need to seek for guidence for excel for excel formula. My predecessor is using some complex formula which I could not get it to work. He was using a mixture of ANDs, IF and SUMIFS.
long story short, we have different offices globally. Each office will incur cost that needs to be allocated to the departments that are operating in their location.
Attached is the staff information that I received in order to calculate the percentage that each department should receive for different costs.
Example:
Office General Cost - Distribute evenly by headcount of staff working in that office BUT do not include Admin staff. This is because Admin staff is part of office general cost.
Electricity - Distribute 80% of electricity for each office to Marketing staff and 20% to the rest of the staff based on headcount BUT do not include admin staff.
What will be the most efficient formula such that everytime I need to update this information, I can just copy and paste the formula all the way down to the last staff. We have over 3000 staff worldwide and so many offices to "allocate cost". The above is just ONE of the many costs that such complexity judgement.
Bookmarks