Dynamic update of values for percentages?

[Indrid_Cold]

Hi all -

Very new to VBA, but I've got a particular project in mind that I'd like to accomplish.

I'm trying to allow a user to generate a table where they can play with percentages and have each percentage dynamically update when a change is made in a cell. Additionally, I would like the redistribution of the change to be weighted according to the values in the other cells. For example;

25%
25%
25%
20%
5%
___
100%

If I change the first 25% to a 10%, I would like the remaining values (25%, 25%, 20%, and 5%) to redistribute the 15% I took away, and to do it proportionate to their value (so not just 15%/4). Each time the user edits a value, the event should trigger (so I think this will be a Change event).

Thanks in advance!

 

[NARAYANK991]

Hi ,

Please upload a workbook which has proper real-life data.

How will the data be entered initially ? The Worksheet_Change event procedure needs to work only when the range is fully populated , and thereafter data is changed in individual cells.

Narayan

 

[Indrid_Cold]

Hi Narayan -

Please find attached the current project I'm working with. Right now, the user selects a cell and presses the button, which prompts them for the Total, the number of columns, and the number of rows. The initial values populating the table is simply Total/Number of Rows.

 

[NARAYANK991]

Hi ,

I am not able to understand anything.

Is the file you have uploaded already having all the initial values ? All I can see is 5 rows where most of the cells have -1 , and then a sixth row has 100. What does this mean ?

Is your data range going to be B3 : K7 ?

Will the user select any cell in this range and change its value ? Should the remaining cells in that column within the range then change to accommodate the total value of 100 ?

Can you take any one column and illustrate with several examples ? Let us consider what should happen if the following cells are changed one after the other : G4 , G7 , G3 , G6 , G5.

What are the possible values these 5 cells can take ?

Assuming any one of the possible values for each of them , what happens as each of them is changed ?

Narayan

 

[Indrid_Cold]

Hi Narayan -

My apologies, it seems that I accidentally attached an older version when I was testing out some other macros. Try the following version. Just so you can have some context, I sometimes need to make grids of percentages for year-by-year analysis of spending data. The percentage breakdown between elements sometimes changes year by year, but they should obviously always equal 100. I wanted to generate a macro so that I could enter the total to sum to, the number of columns (years) and number of rows (divisions of the total). Whenever I edit a value in a cell, I would like the column to adjust as described in my original post.

The logic should be as follows:

- User selects a cell for the table to generate in
- User hits button to start the macro
- User enters in the number that the values should sum to (in this case, 100 since it's percentages)
- User enters the number of columns
- User enters the number of rows
- Table generates
- User starts manipulating percentages.

Does this make sense? Thanks for taking the time to help.

 

[Indrid_Cold]

Just to add - in its current iteration, I got the macro to prompt the user for the first three variables and generate a simple table, but that's all I've gotten to.

 

[Indrid_Cold]

And, finally, to address some of your questions -

- Each column should sum to 100 (or whatever the user designates as "Total" when prompted)
- When a user changes a cell, the difference between the new sum of all cells in the column and the "Total" they have designated should be distributed across the other, unchanged values in the column, proportionate to their value. I.E if you take off 10 from one cell and the value drops to 90, that 10 should be redistributed across the other cells, and the cell with a value of 25 should get more than the cell with a value of 5.
- If you then go and change another cell in the same column, the exact same process repeats itself
- The possible values the cells could take should be all real numbers (again, I use the example of a grid of percentages, but realistically this could be any number)

Does that help?