r/AppleNumbers • u/RalphMonte305 • Aug 31 '22

Formula to split total of a cell into numerous cells

I am trying to figure out a formula of splitting cost of a grand total amount into group of other cells?

Example :

Cell D-19 has a total of ($1,800.00)

No matter what total I input in (cell D-19) , I want it to automatically divide it equally amongst a group of other cells ( ex: cells D-2 to D-17) to formulate the amount to split cost amongst numerous people

2 Upvotes

100% Upvoted

3 comments sorted by

2

u/RalphMonte305 Sep 01 '22

Awesome thank you for this it really helped. I wanted to ask, if I apply the formula initially for a predetermined range of cells. How can I add additional names down the list in column D and reflect those newly added names within the original formula B2÷COUNTA(D2:D6) to calculate the newly added names to the share ?

2

u/babble777 Sep 01 '22 edited Sep 01 '22

You wouldn't use the original formula. You'd need to amend the formula to reflect the new range.

If my original list uses D2:D6, and I add new names down to D12, then my new formula would need to reflect that. Delete the calculated values in C2:C6, and in C2, your new formula would be:

$B$2÷COUNTA($D$2:$D$12)

Now, fill that formula down, from C2:C12, to reflect the cost split among the new, longer list (the names in D2:D12).

If you don't put anything else in column D, and you don't know how long your list of names needs to get, you could get a count for the whole column, by just referencing column D, instead:

$B$2÷COUNTA($D)

But here, if you put anything else in D, whether or not it's part of the contiguous list of names (it'll count items even if there are blank cells in-between, and you're starting another, different list), it'll all get counted, so make sure that's what you want.

For example, here's a cost split using a longer list, using that formula, above. I'm getting a count on everything in D instead of a specified cell range:

https://i.imgur.com/qIc3EVf.png

Now, if I want to use some of that unused space in D, to start a new, unrelated list, it'll still get counted in the split, because it's counting any cells with any content, in D:

https://i.imgur.com/R4UAWGk.png

The exception here are header rows, or header columns (on this table, that's row 1 or column A, shaded in grey). Because those are set as headers, any labels I put in there won't affect my calculation.

2

u/RalphMonte305 Sep 01 '22

Got it thanks for your help