How Do I Create a Formula to Automatically calculate an amount

[raeuchiha]

We have to do leak adjustments where I work, and they have a long drawn out way of doing it. I think I can streamline the process by creating a formula for excel so whatever amount my boss puts in it will give her the amounts automatically.

Ex:

Water : $43.19
Sewer: $61.86

Total: 105.05

So when someone has a leak we take off one third of the amount and the customer is responsible for the rest and the amount is based off of the bill.
The water rate is $9.99
Sewer rate is $24.59

So what information do I need to create the formula so that my boss can just enter in the amount of the bill and itll tell her how much she needs to take off AND what would that formula look like, because I'm sitting here doing a lot of math for a simple answer and there are thousands of adjustments we need to get through.
Please Help

[Sam Capricci]

So when someone has a leak we take off one third of the amount and the customer is responsible for the rest and the amount is based off of the bill.
The water rate is $9.99
Sewer rate is $24.59

taking off 1/3 of the amount of 43.19 is not 9.99, 1/3 of that amount equals 14.40 (rounded) which leaves a balance of 28.79.
so you may need to at least upload a workbook with amounts and desired results included so we can come up with the formulas.
instructions for uploading a workbook are at the top of the post in the yellow banner.

[MarvinP]

Hi raeuchiha and welcome to the forum,

See if the attached does what you want. The rate of the water and sewer don't seem to help in your description above.
Water Leak Compensation.xlsm

[raeuchiha]

I may have explained it wrong. My apologies.

If the bill is $105.05 They split that base amount between water and sewer, but its not split evenly. The % is based on usage. So

ex:
Water usage: 25.02
Sewer usage: 42.01

Total Usage: 67.03

So then it becomes 25.02/67.03 = 37.33%
42.01/67.03 = 62.67%
-Then if it equals 100% we know its correct.

Then we multiply the base amount of what we are supposed to give back. In this case it was $259.73
So $259.73 x 37.33% = $96.96 (so thats the water amount)
$259.73 x 62.67% = $162.77 (thats the sewer amount)
How do I create a formula that will just do all of that automatically after I find the usage?

[JohnTopley]

see attached

=(A4/C4)*E4

=B4/C4*E4