1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

distributed

Discussion in 'Ask an Excel Question' started by Mamdouh Elfors, Jan 20, 2019.

  1. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    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

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.

    Attached Files:

  3. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    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?
  4. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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?
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
    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
    Thomas Kuriakose and deciog like this.
  6. deciog

    deciog Active Member

    Messages:
    127
    Bosco, sensational the change of D$3, I learned one more, thank you, fantastic
  7. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    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

    Attached Files:

  8. Mamdouh Elfors

    Mamdouh Elfors Member

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

    Attached Files:

  9. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    Sorry Busco
    Attach the file again to modify the results manually

    Attached Files:

  10. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.

    Attached Files:

  11. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    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

    Attached Files:

  12. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
    Please see the revised attachment with revised formula.

    Regards
    Bosco

    Attached Files:

  13. vletm

    vletm Excel Ninja

    Messages:
    4,805
    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.

    Attached Files:

  14. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    Dear Bosco
    Why when dragging the equation does not apply to all lines?
    A file attachment explains this

    Attached Files:

  15. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,150
    1] A formula solution with helper columns

    2] See attached file

    3] To hide helper columns if required

    Regards
    Bosco

    Attached Files:

  16. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Mamdouh Elfors
    Formula version in range E3:E14.
    Is it enough short solution?

    Attached Files:

    Last edited: Jan 23, 2019
  17. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    Thanks for the great effort
  18. Mamdouh Elfors

    Mamdouh Elfors Member

    Messages:
    31
    Thanks "vltem" for the simplicity of the solution

Share This Page