Open Office Spreadsheet Formula Question

Factual Questions
1

I haven’t used an Excel type spreadsheet in years, but I used to be really good at formulas. I can’t figure this one out though.

What I am trying to do is get a figure of how profit is divided between two individuals after their relative costs are applied to the payment received for a job.

So for example,

Billy’s Costs Joe’s Costs
Total 250 Total 100

Total Costs: 350
Total Payment Received: 400

Billy is Owed 275
Joe is Owed 125

Balance Left 0

The math I am using for Billy: ((Payment received - Total Cost in Parts) / 2) + (Billy’s costs) = ((400-350)/2)+(250) = 275

The math I am using for Joe: ((Payment received - Total Cost in Parts) / 2) + (Joe’s Costs) = ((400-350)/2)+(100) = 125
I can do that in my head, but what would I put as the formula for it to auto calculate in a spreadsheet program? Does that make sense at all? I am getting what appears to be some sort of number, but it doesn’t come out correct. I have a table of costs for Billy and Joe, and I am using the total costs for each to come up with their figures.

Thanks for the help,

Translucent Daydream

2

Billy’s profit = Payment received - Joe’s owed - Billy’s Costs
Joe’s profit = Payment received - Billy’s owed - Joe’s costs

In terms of percentage’s, it’s just individual profit / combined profit. Is that what you mean?

3

Some thoughts

PEDMAS is the order of operations that you remember when writing an Excel formula

Parentheses
Exponents
Division
Multiplication
Add
Subtract

Without having a spreadsheet open, my “best” answer would be for you to open a workbook and create a formula in the formula bar which references the cells in which you want the values to go.

4

Right. I think. What I am trying to get it to do is take the totals added for billy’s costs, and then joe’s costs, and then take the total payment received and spit out the auto calculated amounts that billy and joe are owed after its all over.

5

I am unsure what you mean by PEDMAS. I just couldn’t do this then?

Billy’s total cost is in A1
Joe’s total cost is in A2
Total Payment for Job is A3
Billy’s cut is =((A3-(A1+A2)/2)+(A1)
Joe’s cut is =((A3-(A1+A2)/2)+(A2)

Maybe I don’t know whats going on. :frowning:

6

Okay, let’s take it a step at a time.

Lets say Billy’s cost is CB
Joe’s cost is CJ
Therefor, total cost is CB+CJ

Billy’s percentage is then CB/(CB+CJ)
and Joe’s percentage is CJ/(CB+CJ).

We’ll call the total payment PT.

So, Billy’s payment would be PT(CB/(CB+CJ)),
and Joe’s would be PT(CJ/(CB+CJ)).

Piece o’ cake.

7

Oh, wait a minute. According to the OP, you’re dividing the profit evenly between the two. That’s different, and should be a bit easier.

Billy’s cost = CB
Joe’s cost = CJ
Total payment = PT

The distributed profit amounts will be (PT-CJ-CB)/2

Billy’s payment is then CB+(PT-CJ-CB)/2,
and Joe’s would be CJ+(PT-CJ-CB)/2,

which is basically the same as what Translucent Daydream came up with. I just removed a set of parentheses.

Also a piece of cake.

8

Come to think of it, you could simplify the equation further.

Billy’s payment=CB+(TP-CJ-CB)/2=CB+.5TP-.5CJ-.5CB=.5BC+.5TP-.5CJ=(CB+TP-CJ)/2
Joe’s payment is (CJ+TP-CB)/2.

I think you’ll find those will also give you the answers you’re looking for.

9

I think I see your problem. You’re not closing out one of your parentheses. For instance, on Billy’s you have:

((A3-(A1+A2)/2)+(A1),

when it should be

((A3-(A1+A2))/2)+(A1).

You’re dividing the costs by two and then subtracting it from the payment, when you need to subtract from the payment before you divide by two.