+ Reply to Thread
Results 1 to 4 of 4

Help with Excel Forrmula

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    2016 (Win10)

    Help with Excel Forrmula


    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.


    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.
    Attached Images Attached Images

  2. #2
    Forum Guru davesexcel's Avatar
    Join Date
    MS-Off Ver
    XL 2010,2016

    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. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    MS-Off Ver

    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:

    G2 =IF([@[Business Group]]<>"Admin",IF([@[Business Group]]="Marketing",(I$2*0.8)/COUNTIF([Business Group],"Marketing"),(I$2*0.2)/COUNTIFS([Business Group],"<>Admin",[Business Group],"<>Marketing")),"")

    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. #4
    Forum Moderator AliGW's Avatar
    Join Date
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)

    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.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • 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.

    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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