# Help with Excel Forrmula

1. ## Help with Excel Forrmula

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.

2. ## Re: Help with Excel Forrmula

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

To add a file to a post

3. ## Re: Help with Excel Forrmula

I would format your data as a table (based on your sample, highlight B1:F14 > Insert > Table)

Then, put the total electric bill in another cell (I chose I2).

Now create a column to show the allocated electricity cost per employee (I put this in column G) using this formula:

With all of this in place, all that you have to do is fill in the employee data (columns B through F and the electricity cost will update automatically. If you want to change the total electric cost, simply change the value in I2.

4. ## Re: Help with Excel Forrmula

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1