# Open Office Spreadsheet Formula Question **URL:** \ **Category:** Factual Questions\ **Created:** [April 27, 2014, 3:15am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093 "2014-04-27T03:15:28Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Translucent\_Daydream](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/translucent_daydream/32/16170_2.png) [@Translucent\_Daydream](https://boards.straightdope.com/u/Translucent_Daydream)\ **Post date:** [April 27, 2014, 3:15am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/1 "2014-04-27T03:15:28Z")
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 ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [April 27, 2014, 3:32am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/2 "2014-04-27T03:32:00Z")
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? ---
**Author:** ![nevadaexile](https://avatars.discourse-cdn.com/v4/letter/n/eb8c5e/32.png) [@nevadaexile](https://boards.straightdope.com/u/nevadaexile)\ **Post date:** [April 27, 2014, 3:33am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/3 "2014-04-27T03:33:02Z")
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. ---
**Author:** ![Translucent\_Daydream](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/translucent_daydream/32/16170_2.png) [@Translucent\_Daydream](https://boards.straightdope.com/u/Translucent_Daydream)\ **Post date:** [April 27, 2014, 3:37am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/4 "2014-04-27T03:37:03Z")
> [@Reply](#): > > 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? 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. ---
**Author:** ![Translucent\_Daydream](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/translucent_daydream/32/16170_2.png) [@Translucent\_Daydream](https://boards.straightdope.com/u/Translucent_Daydream)\ **Post date:** [April 27, 2014, 3:42am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/5 "2014-04-27T03:42:00Z")
> [@nevadaexile](#): > > 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. 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. ☹ ---
**Author:** ![MrKnowItAll](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mrknowitall/32/87_2.png) [@MrKnowItAll](https://boards.straightdope.com/u/MrKnowItAll)\ **Post date:** [April 27, 2014, 4:02am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/6 "2014-04-27T04:02:40Z")
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. ---
**Author:** ![MrKnowItAll](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mrknowitall/32/87_2.png) [@MrKnowItAll](https://boards.straightdope.com/u/MrKnowItAll)\ **Post date:** [April 27, 2014, 4:13am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/7 "2014-04-27T04:13:54Z")
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. ---
**Author:** ![MrKnowItAll](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mrknowitall/32/87_2.png) [@MrKnowItAll](https://boards.straightdope.com/u/MrKnowItAll)\ **Post date:** [April 27, 2014, 4:40am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/8 "2014-04-27T04:40:06Z")
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. ---
**Author:** ![MrKnowItAll](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mrknowitall/32/87_2.png) [@MrKnowItAll](https://boards.straightdope.com/u/MrKnowItAll)\ **Post date:** [April 27, 2014, 5:07am UTC](https://boards.straightdope.com/t/open-office-spreadsheet-formula-question/687093/9 "2014-04-27T05:07:19Z")
> [@Translucent\_Daydream](#): > > 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. ☹ 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.