+ Reply to Thread
Results 1 to 14 of 14

Transfer mathematical surplus from one column to another

  1. #1
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Question Transfer mathematical surplus from one column to another

    Hello group, this is my first post so please consider that I am no expert (wouldn't be here otherwise).
    I hope I'm posting in the right group/forum/

    I am the Treasurer of a small club and I have created a spreadsheet to record payments of Dues and Arrears of members.
    I have 5 columns allowing date and payment for each member to make a maximum of 5 payments throughout the year.
    There is a column called Arrears to record the amount owing from previous years.
    The Dues are £60 this year but may change over the years so I have put that single figure in its own cell outside the table which can be referenced in a calculation.
    My problem in is calculating the amount paid in, easily done by simply adding the amounts, but the arrears must be cleared first before any money comes off the dues amount.
    I also have a column called Remaining which records the total amount outstanding (of both Arrears and Dues)
    The difficulty I have is 'overflowing' the amount paid once the Arrears have been paid, into the Dues column.
    Also, needless to say, no amount can be a negative number.
    I have tried simple addition and subtraction but as I said I'm no expert and while I think this may require a macro to solve (and I wouldn't know where to start on that), there may be other mathematical functions that I am not familiar with or have even heard of.
    I have attached an example table in book1.xlsx (not actual names of members).
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transfer mathematical surplus from one column to another

    Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

    A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

    Since you are a new user of this site, on this occasion, I have changed it for you.

    However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

    *Other search engines are available!!!

    Regards,

    Glenn.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Re: Transfer mathematical surplus from one column to another

    Thanks Glenn - I wasn't sure if I could describe what the problem was but thats a much better title.

    I've just looked at this and realized it needs edited as the two tables do not show the same info. I have posted an updated file
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transfer mathematical surplus from one column to another

    I worked off the first one, so check this out:

    Use variants of this:

    =MIN($C$2,MAX(0,-B5+SUMIF($C$4:$L$4,"Paid",C5:L5)))

    see file.
    Attached Files Attached Files

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transfer mathematical surplus from one column to another

    If you make the correction to the amount paid by Sharon in cell D18, then you can use these formulae in the cells stated:

    M5: =SUMIF($D$4:$L$4,"Paid",$D5:$L5)-N5

    N5: =MIN(B5,SUMIF($D$4:$L$4,"Paid",$D5:$L5))

    O5: =$C$2+B5-SUM(M5:N5)

    then copy the 3 formulae down as required.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Re: Transfer mathematical surplus from one column to another

    Glenn, Pete_UK
    Thank you both for your replies.
    Glenn - yours worked a treat and even keeps the conditional formatting I has set in columns M N & O.
    Pete, when I copied your formulae into the top table it shows Sharon as having -15 remaining. I'll check again.

    Sharon 25 06/10/2020 100 75 25 -15

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transfer mathematical surplus from one column to another

    You're welcome.



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

    It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

  8. #8
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Re: Transfer mathematical surplus from one column to another

    Nope it is -15 in that cell.
    Conditional formatting is not a concern anyway as having checked it its not the same for all columns - that's my mistake.
    I will play around with some further figures but I think that's it solved.

    Glenn, just one thing - you referred to $c$4:$l$4 but these are column headers and not numbers to be manipulated. I don't understand the function so will go away and look it up with examples.
    Once again thank you both.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transfer mathematical surplus from one column to another

    That is correct. The formula says if the header row (C4 to L4) is "Paid" sum the values in C5 to L5.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transfer mathematical surplus from one column to another

    Did you correct Sharon's payment in cell D18? i.e. 100 in the upper table, but 85 in the lower table. She only owes 85 in total (60 + 25), so why should she pay you 100 ?

    Pete

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Transfer mathematical surplus from one column to another

    Just seeing Pete's last post... if someone has overpaid (however unlikely thta might be!!) do you need another column: "Surplus Paid", using htis formula:

    =MAX(0,SUMIF($C$4:$L$4,"Paid",C5:L5)-($C$2+B5))
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Re: Transfer mathematical surplus from one column to another

    Glenn,
    Yes that column would be useful, some members do pay forward to the next year in full or in part.
    That mistake was entirely mine as you can tell, it was me entering example figures into the table prior to posting.
    lucky it turned out that way - I got a bonus !
    thank you.

  13. #13
    Registered User
    Join Date
    01-09-2021
    Location
    U.K.
    MS-Off Ver
    2010
    Posts
    6

    Re: Transfer mathematical surplus from one column to another

    Pete_UK
    In reply to your email as I cant reply directly

    Pete,
    It was an oversight on my part, I apologize. I was playing around with figures to provide examples prior to posting. I missed that one.
    thank you for your speedy reply, it has helped me a lot.

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Transfer mathematical surplus from one column to another

    It's not me that sends you an email - it's the forum software to notify you that there has been a response to a thread you have subscribed to, so you can't reply directly to it. You can turn those notifications off in your User CP, but then you wouldn't know if someone replies, so you have to visit the forum more often.

    Pete

+ 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. [SOLVED] Split Surplus Funds
    By robotlust in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2020, 10:05 AM
  2. Column A Identifier, Coumn B Values, Perform Mathematical Function
    By anon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2012, 08:50 PM
  3. [SOLVED] Macro to delete surplus rows.
    By lander2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2012, 03:59 PM
  4. Compare unique references in same column of two files, clearing row of "surplus"
    By fahnskap in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2012, 05:46 AM
  5. Variable repayment, only when cash surplus is >$5000
    By rockville9 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-25-2010, 04:27 AM
  6. Sum the surplus
    By yogeshmaney in forum Excel General
    Replies: 7
    Last Post: 09-05-2009, 08:38 AM
  7. [SOLVED] How do I show a surplus number in an entry (on the plus side)
    By SUSIEQ450 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2006, 11:20 AM

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