+ Reply to Thread
Results 1 to 25 of 25

Add up to certain number - Equity & loan

  1. #1
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Add up to certain number - Equity & loan

    Dear All,

    I am facing issue I cannot overcome, would appreciate help on the below. Also, please see attached file, I hope it reinforces the description below.

    1)I am after a formula that will allow to 'drop' into the equity row (row no 7) the expenditure amounts up to total equity of 41 300 (as per example) and thereafter show zero. Between month 1 -8 total is 58 800; I do not know how to get some clever formula to show only required balance to get to 41 300 in Equity row.
    2) For the loan I am after formula that starts dropping to loan row amounts only when equity amount is reached.

    I would very much appreciate help on this. Thank you!


    Equity Total: 41 300

    Month1 Month2 Month3 Month4 Month5 Month6 Month7 Month8 Month9 Month10 Month11 Total
    Expenditure 2 100 2 100 2 100 7 100 2 100 9 100 12 100 22 100 2 100 2 100 2 100 65 100

    Equity
    Loan




    Kind regards
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Add up to certain number - Equity & loan

    Pl see file

    In D7
    =IF(($B$2-SUM($D$5:D$5))>0,MIN($B$2-SUM($D$5:D$5),D$5),0)

    In D8
    =D5-D7

    Copy both formulas across .
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Hello,

    Thank you very much for looking at this. Much appreciate.
    There seems to be something wrong in the formula as it takes equity total in row 7 up to amount 29 200 but it should be 41 300 (as per cell B2).
    For the row 8 loan total counts good but is there some clever formula for loan to link it with B2 (equity total) and equity row 8. So it starts dropping amounts to loan row when it goes beyond 41 300?

    Thank you very much kvsrinivasamurthy.

    Regards

  4. #4
    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
    79,369

    Re: Add up to certain number - Equity & loan

    Please provide a workbook with the figures you are expecting entered manually. 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.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Add up to certain number - Equity & loan

    Here is revised formula for D7

    =IF((SUM($D$5:D$5))<$B$2,D$5,MAX($B$2-SUM($C$5:C$5),0))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Hello,

    Please see attached.

    Many thanks
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    kvsrinivasamurthy,
    I posted reply to AiGW before noticed your answer. Thank you very much !!!!! now it works great.
    Do you have some idea on formula for loan row so that it starts dropping in numbers when over B2 cell(equity treshold)?

    Many thanks again kvsrinivasamurthy, very helpfull

  8. #8
    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
    79,369

    Re: Add up to certain number - Equity & loan

    The workbook attached to post #5 has the values you want in the loan row ...

  9. #9
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    AliGW, yes kvsrinivasamurthy sorted, much appreciated.
    However, it would be of additional value to me to have formula in loan row that would be linked to row expenditure and show numbers only over B2 cell (total equity). So in general the results would be the same but I could adopt that formula elsewhere. As need it sometimes. it would be useful for me very.

  10. #10
    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
    79,369

    Re: Add up to certain number - Equity & loan

    I don't understand what you are asking for. Do you mean this in D8 drag copied to the right?

    =D5-IF((SUM($D$5:D$5))<$B$2,D$5,MAX($B$2-SUM($C$5:C$5),0))

  11. #11
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    AliGW, absolutely yes great! thank you!

    kvsrinivasamurthy, All sorted now. thank you !

  12. #12
    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
    79,369

    Re: Add up to certain number - Equity & loan

    Aha! Great.

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

  13. #13
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Alright will do, many thanks again!

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Add up to certain number - Equity & loan

    Thanks for feed back and rep.

  15. #15
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    I have one more question on this, as just noticed.

    In my file I am showing expenditure as negative but equity and loan should come/show as positive. Please see attached.
    Are you able to amend these formulas for equity and loan ?

    Cell b2 total equity is positive number, all with exception of Expenditure row which is negative stays the same.

    Sorry and thanks again !
    Attached Files Attached Files

  16. #16
    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
    79,369

    Re: Add up to certain number - Equity & loan

    Please remove the SOLVED tag if you require further help.

  17. #17
    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
    79,369

    Re: Add up to certain number - Equity & loan

    Change the custom formatting pattern to this:

    _* #,##0_-;#,##0_-;_* ""??_-;_@_-

    But only for the equity and loan cells!

    Excel 2016 (Windows) 32 bit
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    7
    Equity
    2,100
    2,100
    2,100
    7,100
    2,100
    9,100
    12,100
    22,100
    2,100
    2,100
    2,100
    8
    Loan
    Sheet: Sheet1

  18. #18
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Hi, I have amended the formula so with the expenditure presented as negative number it shows positive equity and loan. Please see attached.
    However, once I have addopted that formula into the other excel file it 1) does not show last number for equity row, as per attached. 2) in loan row shows total insted of splitting cost. Any idea why? I have office 365, I am using '';'' seperation insted of '','' but I have changed that and seems no issue. I really do not know.
    Would appreciate your comments / help on this.
    Attached Files Attached Files

  19. #19
    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
    79,369

    Re: Add up to certain number - Equity & loan

    We’d need to see the formulae in place in the workbook where it is not working to check that you have set then up correctly.

  20. #20
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Hi AliGW,
    Please see attached spreadsheet.
    Total equity is in cell G30. Expenditure Q59:ER59. Equity Q88:ER88. Loan Q88:ER88.

    You will note that in AQ88 and AQ89 , it does not split the value between equity and loan. I do not know why is it, formula seems right at least to me. Appreciate help on this.
    Attached Files Attached Files

  21. #21
    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
    79,369

    Re: Add up to certain number - Equity & loan

    Probably because you have messed up the formulae by adding minus signs all over the place. You should do the formatting as suggested earlier, not via the formula.

    Start again. Apply the formula as it has been given to you above and go from there.

  22. #22
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    I have actually tried do several different things but it does not work, I think to tried everything in my capabilities incl. applying your pattern, and amending formula etc. Without negatives it seems come out even more out of blue results.
    The formula I was given I had to change as expenditure must be negative as input data and in formula given it was based on expenditure positive numbers. Unfortunately I am not expert on this, thus do not see straightforward fix. If possible to amend in the spreadsheet I attached previously that would be great. If anybody see what is actually wrong and how to fix. But I see it might be problematic to do.
    Anyhow many thanks for your time.

  23. #23
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Add up to certain number - Equity & loan

    Revised formula
    In D7

    =IF((-1*SUM($D$5:D$5))<$B$2,-1*D$5,MAX($B$2+SUM($C$5:C$5),0))

    In D8

    =-1*(D5+D7)
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    02-11-2020
    Location
    Poland
    MS-Off Ver
    2019
    Posts
    14

    Re: Add up to certain number - Equity & loan

    Amended all works good now, I have been doing mistake whilst applying in my spreadsheet i.e. range in first sum was the same as in the other sum. But range in the second sum should end one cell before.

    Thanks !!!!

  25. #25
    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
    79,369

    Re: Add up to certain number - Equity & loan

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel Loan File; How to Attend to Loan Files with Varying Payment Amounts?
    By PivotTablePSHomage in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2018, 08:10 PM
  2. Replies: 3
    Last Post: 07-24-2016, 07:33 PM
  3. [SOLVED] Secondary Loan Pricing Calculation (price of loan being bought/sold)
    By Romsky in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 07:02 PM
  4. Replies: 3
    Last Post: 06-02-2012, 07:50 PM
  5. Home Equity Loan
    By scantor145 in forum Excel General
    Replies: 1
    Last Post: 01-10-2012, 01:24 PM
  6. Replies: 0
    Last Post: 01-30-2006, 06:30 PM
  7. Replies: 2
    Last Post: 08-02-2005, 04: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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1