# distributed

#### Mamdouh Elfors

##### Member
I would like to have the number in the first column to be distributed on the second column with 25% in each cell
and if another value is added in the first column, 25% of it would be added to what is remaining of the first number as showen in the table.
This would continue till no values are left

#### Attachments

• 10.2 KB Views: 11

#### vletm

##### Excel Ninja
Mamdouh Elfors
Are Your sample 'distribution'-values as You've wanted?

Sample usage:
> Change B-column values as needed.
> The last solved row is then A-column has text "Total"
> My version 'distribution'-values comes to D-column.

#### Attachments

• 17.1 KB Views: 5

#### Mamdouh Elfors

##### Member
Mamdouh Elfors
Are Your sample 'distribution'-values as You've wanted?

Sample usage:
> Change B-column values as needed.
> The last solved row is then A-column has text "Total"
> My version 'distribution'-values comes to D-column.
The sum of the results don't match the sum of the original values. I wanted to distribute the values in the first column on the second column 25%, so 200 would be 50 in 4 rows, and if you add another value in the first column on the third row, that would make the thrid and forth row value 150 and the fifth and sixth row value 100 because the values stack up.

If possible, is there a solution to do this using an equation not VB?

#### vletm

##### Excel Ninja
Mamdouh Elfors
Did You skip next line?
> Are Your sample 'distribution'-values as You've wanted?
= How did You get those?

Many things are possible
it's challenge to modify anything!
...
and why not with VB?

#### bosco_yip

##### Excel Ninja
Perhaps,

1] In D3, copied down to D14

=IF(ROWS(\$1:1)>4,IF(SUM(B\$3:B3)-SUM(D2:D\$3)=0,0,LOOKUP(9^9,B\$3:B3)*0.25),SUM(\$B\$3:B3)*0.25)

2] In D15: =SUM(D3:D14)

Regards
Bosco

#### deciog

##### Active Member
Bosco, sensational the change of D\$3, I learned one more, thank you, fantastic

#### Mamdouh Elfors

##### Member
Mamdouh Elfors
Did You skip next line?
> Are Your sample 'distribution'-values as You've wanted?
= How did You get those?

Many things are possible
it's challenge to modify anything!
...
and why not with VB?
You have modified the values I have deleted the last line which includes the total
The values were manually distributed
I want to divide the values in column B to be the same in column C but divided in each line to a quarter of the value
In the case of adding another value in column B are taken into account
Please consider values as an example only to work on more data

It explains how you got the results

#### Attachments

• 17.8 KB Views: 3

#### Mamdouh Elfors

##### Member
Perhaps,

1] In D3, copied down to D14

=IF(ROWS(\$1:1)>4,IF(SUM(B\$3:B3)-SUM(D2:D\$3)=0,0,LOOKUP(9^9,B\$3:B3)*0.25),SUM(\$B\$3:B3)*0.25)

2] In D15: =SUM(D3:D14)

Regards
Bosco
Thanks Bosco
However, when applying your formula, the results differed after adding 80 in C7 and 100 in C9

#### Attachments

• 10.4 KB Views: 1

#### Mamdouh Elfors

##### Member
Thanks Bosco
However, when applying your formula, the results differed after adding 80 in C7 and 100 in C9
Sorry Busco
Attach the file again to modify the results manually

#### Attachments

• 10.4 KB Views: 1

#### vletm

##### Excel Ninja
Mamdouh Elfors
Okay ...
if You meant that ...
then You should give at once Your previous file to get clear idea.

You also deleted that one my version 'need to be there' ... hmm?

I wrote now two 'new' versions ...
... something which You let me know
'Sheet1'
= 'My ver' ... as I would do it
= 'Your ver' ... as You wanted to do it

'Sheet2' -- My ver
tries to explain how it would done with 'basic formulas'
>
I didn't start to make 'Your ver' with formulas,
because I didn't have any reason.

> Usage is same as with my original.

#### Attachments

• 21.4 KB Views: 2

#### Mamdouh Elfors

##### Member
Mamdouh Elfors
Okay ...
if You meant that ...
then You should give at once Your previous file to get clear idea.

You also deleted that one my version 'need to be there' ... hmm?

I wrote now two 'new' versions ...
... something which You let me know
'Sheet1'
= 'My ver' ... as I would do it
= 'Your ver' ... as You wanted to do it

'Sheet2' -- My ver
tries to explain how it would done with 'basic formulas'
>
I didn't start to make 'Your ver' with formulas,
because I didn't have any reason.

> Usage is same as with my original.
Thank you "vletm" for your great effort
I can not edit the VB code so I do not know the VB
My ver: start from C17
The amount :start from D17
Your ver : start from H17
Thank you again

#### Attachments

• 19.8 KB Views: 0

#### bosco_yip

##### Excel Ninja
Sorry Busco
Attach the file again to modify the results manually
Please see the revised attachment with revised formula.

Regards
Bosco

#### Attachments

• 11.5 KB Views: 4

#### vletm

##### Excel Ninja
Mamdouh Elfors
Why You cannot edit code? ...
So, I gotta do my version to right side of those.
Usage is same as written in my previous file as well as 'formula'-version.

#### Attachments

• 19.6 KB Views: 3

#### Mamdouh Elfors

##### Member
Dear Bosco
Why when dragging the equation does not apply to all lines?
A file attachment explains this
Please see the revised attachment with revised formula.

Regards
Bosco

#### Attachments

• 12.6 KB Views: 4

#### bosco_yip

##### Excel Ninja
Dear Bosco
Why when dragging the equation does not apply to all lines?
A file attachment explains this
1] A formula solution with helper columns

2] See attached file

3] To hide helper columns if required

Regards
Bosco

#### Attachments

• 21.1 KB Views: 6

#### vletm

##### Excel Ninja
Mamdouh Elfors
Formula version in range E3:E14.
Is it enough short solution?

#### Attachments

• 19.9 KB Views: 5
Last edited:

#### Mamdouh Elfors

##### Member
1] A formula solution with helper columns

2] See attached file

3] To hide helper columns if required

Regards
Bosco
Thanks for the great effort