Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Atlanta, Georgia, USA
    Posts
    276
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2K calculation problem... (Excel 2000)

    Okay, this is a tough one for me. I can't seem to wrap my brain around it. Here it is:

    I have a spreadsheet that I use for a phone bill for the company I work for. I also have a 3rd party program that tells me the number of calls the 5 "divisions" made during the monthly billing cycle. On this spreadsheet I plug in the amount of the new bill and then plug in the number of calls made by each "division". Doing that calculates a percentage (what percentage of all the calls made that month did each given division make) and then it is calculated how much each division should pay for their phone useage.

    I inherited this spreadsheet from the guy whose place I have filled. In the bill I can see that some of the charges need to specifically go to certain divisions instead of being spread across each division. The only problem is that I can't figure out how to edit the formula's to allow for this extra charge. Here are the calcs being used:

    =SUM(B8/B18) (this one calc's the percentage of calls the division made out of the total calls [division calls are in cell b8, total calls is in cell b18])

    =SUM((C8*B4)+D8) (this one calc's how much the division should pay for it's phone useage based on the percentage of calls made [cell c8] and any other charges [cell d8])

    =SUM(D8:E16) (this one calc's the other column charges and the cost column and sums them all up)

    Does this make sense? Now that I've decided to actually look at the bill closely I can see that some divisions need to be paying for certain items but I can't figure out how to make the number match up, because as I add things to the other column it adds to the total and causes the total to go over what it actually is. Any help would be greatly appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2K calculation problem... (Excel 2000)

    I *THINK* I can understand what you are getting at but is there _ANY_ way you could post an example - names etc censored out of course - as it would make it much simpler for us to give you a simple answer.
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2K calculation problem... (Excel 2000)

    Without being able to grasp what you need yet (I too would like a sample), I do have one comment:

    The first two SUM's in your example are unnecessary. They all SUM a single number, which is a bit overdone <g>.

    So:

    =SUM(B8/B18)

    gives identical results as:

    =B8/B18

    For your problem you probably need the SUMIF function, but we need a sample to be sure.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Atlanta, Georgia, USA
    Posts
    276
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2K calculation problem... (Excel 2000)

    Here's a sample, thx for the help!

    Total Amount Due $7,222.98
    Monthly $7,222.98

    Division Calls % Other Cost

    ACA 6,734 10.50% $31.00 $789.42

    ASO 16,896 26.35% $0.00 $1,902.91

    ATC 7,709 12.02% $14.30 $882.53

    HMA 14,828 23.12% $0.00 $1,670.00

    WAC 17,966 28.01% $10.80 $2,034.22

    Total 64,133 $7,335.18

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Atlanta, Georgia, USA
    Posts
    276
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2K calculation problem... (Excel 2000)

    Notice how the monthly at the top is the correct figure for the bill but at the bottom the total off because of the additional charges. That's what I'm having a hard time figuring out. Thx again for the help.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Atlanta, Georgia, USA
    Posts
    276
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I figured it out!

    I figured out how to do it. Here's what I did. I added a "Less Other charges" cell reference underneath the a cell that had a value for the monthly amount (cell b4) we were charged. I added up the other charges (using a formula) and placed it in cell b5. Then in cell b6 I used a formula to subtract the b5 from b4 to give an accurate total that needed to be divided among the divisions. I then edited the formula's that referenced cell b4 and had them reference cell b6 instead (all of the cells under the cost column except the final total cell). In the final total cell I had it simply sum the cells directly above and the total completely matches. Does that make sense? At any rate, it worked and I don't know why it took me so long to figure it out. Thank for all of your help.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2K calculation problem... (Excel 2000)

    Uhm, you misunderstood a bit, sorry if I wasn't clear.

    Specific: Could you upload a sample *spreadsheet* with real data and the formula's you have (edited to show a relevant part and to remove confidential information of course...)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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