• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Distribute content of one cell into others

S

singh_deepak

New Member
Hi

Can someone please help. In the table below, if i change the value in one cell, the change should be distributed in other cells. For example, if I change the value for Thu then this should change the other cells proportionately to their original distribution so that the sum remains 1 or 100%.

Mon 24.0%
Tue 24.2%
Wed 23.5%
Thu 2.5%
Fri 18.8%
Sat 4.1%
Sun 2.9%
 
Hi ,

If this change is to happen in the cell which has the values , then only VBA can help.

If you are willing to enter the change / changed value in a different range of cells , so that the original data is available , and if the redistributed values can be in yet another set of cells , then a formula-based solution is possible.

Narayan
 
Hi,

Thank you Sir!!!

You are great!! Can you please help me understand the logic.

First you calculated the total difference in the Cell Helper cell.

Then you added ratio of the difference to the original distribution number.

just one question, B2 + $I$2 * B2/($B$10 - $I$2 - $C$10), Why did you subtract $C$10 from the base to calculate the ratio of the change?

Thanks again,
 
Hi ,

When we are distributing the difference , we need to know what percentage this difference was of the original amount ; thus , a 2% difference when the original amount was 24% will be treated differently from a 2% difference when the original amount was 4%.

What I mean to say is that we know that a category is 24% originally , when the sum of all categories is 100% ; now another category has been reduced from its original 14% to 3% , which is a reduction of 11%.

Because the new value is 3% , it means that the remaining categories should add up to 97% ; however , originally , the remaining categories would have added up to 86% (100% - 14%).

Thus , the reduction of 11% should be spread over 86% , and not 100%.

Hence , if another category was originally 20% , it will now become 20% + 20% * 11%/86%.

However , we have no idea of this original amount of 14 % ; one way to get it is therefore to add the sum of the changed values (essentially only one value can be changed at a time) to the difference in the helper cell.

Since Original Value - Changed Value = Difference ,

Original Value = Difference + Changed Value.

Narayan
 
You must log in or register to reply here.
Back
Top