+ Reply to Thread
Results 1 to 15 of 15

Divide the amt amongst on rational basis

  1. #1
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Divide the amt amongst on rational basis

    Dear Team,

    Happy New Year to all ....

    PFA the excel file showing the working of Limit and Outstanding bal.

    I need to give a formula in col. M which allocates amount of cell M21 between cust ID "a" on rational basis.
    Similarly, amount of cell M22 needs to be allocated between cust ID "b" and so on.

    There is 1 condition.
    Total amt in col N (Total O/s amt) should not exceed exposure limit in col D.

    There are approx 50000 rows having diff. cust ID and A/c no. in the database.

    Is there anyway we can automate this ?

    Please help....
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Divide the amt amongst on rational basis

    If I understand correctly then the following formula should work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If I am missing something please provide your expected values for M3:M17 so that we can attempt to replicate those values automatically using either formulas or codes.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    Thanks a lot...
    I will get back to you.
    Last edited by lalaarif1; 01-11-2018 at 02:35 PM.

  4. #4
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    Dear JeteMc,

    you may reshuffle the rows considering actual scenario and allocate the amount on FIFO basis. But the amt. should not exceed Balance exposure limit (Col F)

    Please assist.

  5. #5
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    How do i re-attach the revised file with desired output?

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Divide the amt amongst on rational basis

    Manually put in the expected values for cells M3:M17, save to your hard drive, then upload to this site.
    To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    You could also type a post that lists the expected values of M3:M17.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    PFA the file for your ref.

    I have updated required data in column M.

    Rows have been shuffled up-side-down since rows would not be in perfect sequence.

    Please assist,
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Divide the amt amongst on rational basis

    The following formula yields the same values as those manually placed in column M when pasted into M3 and copied down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    wow...

    Thanks a lot.

    You made my day ....

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,335

    Re: Divide the amt amongst on rational basis

    Moved to the correct forum section.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  11. #11
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    JeteMc,

    PFA the revised file (refer tab pivot).

    Is there any formula which gives me the desired out of col B ?

    Please assist.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    JeteMc,

    The above question in continuation of the chain of mail...

    Please ..... help.

  13. #13
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Divide the amt amongst on rational basis

    The formula that is in B5 is a good place to start, I just modified it a bit so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It yields the same values as those that were previously in column B.
    Let us know if you have any questions.

  14. #14
    Registered User
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    86

    Re: Divide the amt amongst on rational basis

    wow... thanks a lot.

    Its working fine.

    1 more quick question. Its all related to the chain of mails. Bosses keep asking for new output. So please help.

    PFA the file.

    We have to arrive the output in col Y.
    I have manually updated the desired output in col Y.

    Your help would make my day.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    4,414

    Re: Divide the amt amongst on rational basis

    I don't understand the rational of the figures that are in column Y. For customer 'a' the balancing figure is the first value (associated with A/c No. a01). For customer 'b' the balancing figure is the last value (associated with A/c No. b04). I can not figure out why the positioning of the balancing figures is different, and if I can't figure it out, I can't write a formula to do it. Perhaps if you could explain why the balancing figures are in different positions for different customers someone might be able to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1