Results 1 to 7 of 7

20020319, 19:02 #1
 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.

20020319, 21:41 #2
 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.
Grüße

20020320, 06:03 #3
 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.jkpads.com
Professional Office Developers Association

20020320, 13:15 #4
 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

20020320, 13:16 #5
 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.

20020320, 13:38 #6
 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.

20020320, 13:39 #7
 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.jkpads.com
Professional Office Developers Association