+ Reply to Thread
Results 1 to 15 of 15

Divide the amt amongst on rational basis

  1. 01-02-2018, 02:43 PM #1
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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
    Reply With QuoteRegister To Reply
  2. 01-04-2018, 05:45 PM #2
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    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.
    Reply With QuoteRegister To Reply
  3. 01-11-2018, 02:30 PM #3
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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.
    Reply With QuoteRegister To Reply
  4. 01-11-2018, 03:07 PM #4
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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.
    Reply With QuoteRegister To Reply
  5. 01-11-2018, 03:08 PM #5
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    Re: Divide the amt amongst on rational basis

    How do i re-attach the revised file with desired output?
    Reply With QuoteRegister To Reply
  6. 01-11-2018, 03:32 PM #6
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    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.
    Reply With QuoteRegister To Reply
  7. 01-14-2018, 06:57 AM #7
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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
    Reply With QuoteRegister To Reply
  8. 01-14-2018, 11:34 AM #8
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    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.
    Reply With QuoteRegister To Reply
  9. 01-14-2018, 01:43 PM #9
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    Re: Divide the amt amongst on rational basis

    wow...

    Thanks a lot.

    You made my day ....
    Reply With QuoteRegister To Reply
  10. 01-14-2018, 01:48 PM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,288

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  11. 01-14-2018, 01:48 PM #11
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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
    Reply With QuoteRegister To Reply
  12. 01-14-2018, 01:51 PM #12
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    Re: Divide the amt amongst on rational basis

    JeteMc,

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

    Please ..... help.
    Reply With QuoteRegister To Reply
  13. 01-14-2018, 05:10 PM #13
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    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.
    Reply With QuoteRegister To Reply
  14. 01-15-2018, 09:02 AM #14
    lalaarif1
    lalaarif1 is offline
    Forum Contributor
    Join Date
    05-11-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2007 / 2010
    Posts
    270

    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
    Reply With QuoteRegister To Reply
  15. 01-16-2018, 09:57 PM #15
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. total on the basis of color
    By Azam Ali in forum Excel General
    Replies: 1
    Last Post: 04-23-2015, 02:38 AM
  2. convert 360 days basis to 365 days basis in excel 2007 TBILLSYIELD function
    By Dilshad in forum Excel General
    Replies: 5
    Last Post: 03-22-2012, 04:51 AM
  3. Excel 2007 : Calculation on the basis of IF
    By habibian88 in forum Excel General
    Replies: 9
    Last Post: 09-19-2011, 04:32 AM
  4. Rational Number
    By ElmerS in forum Excel General
    Replies: 1
    Last Post: 03-06-2010, 02:55 PM
  5. Basis SUM macro
    By hunsnowboarder in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2008, 06:26 PM
  6. [SOLVED] Getting the particular value on the basis of given criteria ?
    By Luqman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2006, 03:44 AM
  7. Rational exansion of a decimal number
    By Leo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2005, 01:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1