CODEWITHSUNDEEP
excelmathformulas
Joshua Spooner
Joshua Spooner

Reputation: 19

In Excel, how would I divide a sum, and have one cell with a different percentage

I am looking to divide a single sum among various participants.

I have 12 people listed in a column, the next column will contain the divided sum amounts. The cell at the top of the sum column (C13) will have an amount input into it ($90.63) and the formula will then divide that amount among the 12 cells under it.

people   | share     |
         |     90.63 |
-------- | --------- |
person01 | =(C13/12) |
...
person12 | =(C13/12) |

That part I got figured out. so if column C cell 13 had an amount of $90.63 entered, the formula in the 12 cells under it would read =SUM(C13/12), each cell with this formula would then contain a divided amount to show each persons share ($7.55) of the entered amount ($90.63).

However, if one of those 12 people were to only get half of this sum amount share, how do I calculate that while giving the remaining people the extra share?

In essence, if person01 would only get =(C13/24) how do i calculate the share of the remaining 11?

people   | fair share | actual share
         |      90.63 |
-------- | ---------- | ------------
person01 |  =(C13/12) |    =(C13/24)
...
person12 |  =(C13/12) |            ?

Upvotes: 0

Views: 4703

Answers (4)

skkakkar
skkakkar

Reputation: 2828

Referring to my comments "For one person $C$13/24 and for rest 11 =($C$13-($C$13/24))/11 " I have rechecked and the formula proposed earlier gives correct results. Screenshot is appended below. Moreover this solution is based on Excel terminology rather than Maths.

Screenshot showing results

Upvotes: 1

rajah9
rajah9

Reputation: 12339

Let x be the amount that everyone (but one) pays. Let .5x be the amount that one person pays. The sum, .5x + 11x = 90.63. If you let 11.5x = 90.63, then x=7.88087. The first person pays $3.94 and the rest pay $7.88.

You can double check; 3.94 + 11 * (7.88) = 90.63.

(And yes, this is an algebra problem, not an Excel problem. Nevertheless...)

Create a column of shares that each person would have. Place a .5 in the first row and 1 in the rest. (The sum at the bottom is 11.5, in B15.)

Create a column with 90.63 at the top (here, C1). In C3, place the equation =C$1/$B$15*B3, which says to take the 90.63 and divide it by the total number of shares (11.5) and multiply by how many shares this person is assigned.

Screen shot

Besides them adding up to 90.63, please note that the .5 share is exactly half the amount of the rest.

(For the other answers that are dividing by 11 and such: would you please try your solution and see if you are adding up to 90.63 and whether person 1 pays 3.94?)

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Place the total in C1 and then run this:

Sub cropier()
    Dim V As Double, i As Long

    V = Range("C1").Value
    i = 2
    Do Until V < 0.01
        Cells(i, "C").Value = Cells(i, "C").Value + 0.01
        V = V - 0.01
        If V < 0.01 Then Exit Sub
        For i = 3 To 13
            Cells(i, "C").Value = Cells(i, "C").Value + 0.02
            V = V - 0.02
            If V < 0.01 Then Exit Sub
        Next i
        i = 2
    Loop
End Sub

enter image description here

The macro deals out the money one or two pennies at a time until the money is completely distributed.

The person at C2 gets only half the shares of the others.

Upvotes: 0

Vishesh
Vishesh

Reputation: 334

for 1st person = c13/2 and for others =($c$13)/2/11

Upvotes: 0

Related Questions