how to equally distribute the distributed from absolute cell among the columns or rows

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Pl find here. It is having only limited differences. I want to know, whether you could automate the distribution, so it exactly match the total value in distribution, like rounding of uneven numbers distributed among selected rows or columns. Sorry, if i have asked anything wrong

Good question but I don't have any suggestion from my end

Because distributing the amount equally will result several decimal places and rounding it to the 2 decimals will ignore the actual decimals and which is causing unequal total at the end.

hi rajuj. i might be wrong, but surely there has to be some sort of logic you wish to apply. otherwise, Excel won't be able to help. you need to always ask yourself how would you want to do it manually? how do you wish to distribute 1 cent/paise to all 7 of them equally? it's impossible. or if it's 3 to 7 cents like you mentioned.

for eg. you might want to distribute whatever that is left to the last person or to the highest amount invested (got to consider when there's 2 people who contributed the same), etc. it will probably never be fair, but it's a logic you need to provide for it to work.

in the simplest of case, where i distribute to the last person would be this in C2 & copied down:
=IF(A3<>"",ROUND(B2/$B$9*$C$9,2),$C$9-SUM(C$1:C1))

D8, by the way, can simply be:
=SUM(C2:C8)

but as i said, i might be wrong. Sixthsense has helped you gather for assistance, so see if there are other opinions. meanwhile, you can start thinking about the logic

@ yudlugar,

May be something like this?

In C2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(ROUND(B2/$B$9*$C$9,2)=MAX(ROUND($B$2:$B$8/$B$9*$C$9,2)),ROUND(B2/$B$9*$C$9,2)+($C$9-SUM(ROUND($B$2:$B$8/$B$9*$C$9,2))),ROUND(B2/$B$9*$C$9,2))

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down...

@ yudlugar,

Oh God!!! You are drilling it even further

No.... my brain will collapse if I do that lol

Originally Posted by rajuj

is this formula could be applied to all kind of round problems. Will it correctly distribute to the proportion [/U][/B].

well, it depends on what you want to do with it really. it simply does what you are already doing, but any extra amount that cannot be distributed will end up with the last person

Why A3<> in your formula. Is there any significance

the formula starts from A2. so what it does is to say that if the next row (A3) is NOT empty, do your ROUND formula. when it's finally empty (at A9), take the total (C9) minus the summation of whatever amount above. so C8 is essentially C9 - SUM(C2:C7)

it's certainly not the fairest method, but the easiest. as mentioned, you would need to set the logic for us. there are probably 101 ways to do it & both yudlugar & Sixthsense have shared a few ways. so before you set a logic, there's no point to dwell too deeply on the formulas

hI, BENI SIR,
Yours is somewhat is understood by me. Their formula though excellent, i could not understand. Do we need that much precision.
Whenever, there is huge amount, adding and substracting and finally arriving amount, if it is to be distributed to the total to some proportion, your formula , i think make tally the total with the individual distribution. Otherwise, one has to do the vertical, horizontal check to find the difference, even though smaller and adjust. Normally, one would adjust by adding the required difference to the higher amount arrived, so that it does not create a problem. And this is the logic the normal distribution.
I will check on larger digits and tell you , how it goes.
By the by, if there is some uneven amount, and that has to be apportioned on no basis. How to get the total tally in this case.
For eg. If there is 195
to be divided among 3, it is divisible and hence distributed . But if if 199 is to be distributed among 3 persons, on no basis, like in my example, what is the formula. Is that, the other friends trying. I will try their formula also. Thanks to all of you . expecting your answer