• 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.

distributed

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

  • MMors.xlsx
    10.2 KB · Views: 12
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

  • MMors.xlsb
    17.1 KB · Views: 6
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.
Thank you for your reply,
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?
 
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
but without answers,
it's challenge to modify anything!
...
and why not with VB?
 
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
but without answers,
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

  • MMors.xlsb
    17.8 KB · Views: 3
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

  • MMors11.xlsx
    10.4 KB · Views: 1
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

  • MMors (1).xlsb
    21.4 KB · Views: 2
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
Please make:
My ver: start from C17
The amount :start from D17
Your ver : start from H17
Thank you again
 

Attachments

  • MMors (1).xlsb
    19.8 KB · Views: 0
Mamdouh Elfors
Why You cannot edit code? ...
Your 'Please make:' -references didn't match to that file.
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

  • MMors11(2).xlsb
    19.6 KB · Views: 3
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

  • Distribution(1).xlsx
    21.1 KB · Views: 6
Back
Top