+ Reply to Thread
Results 1 to 6 of 6

find max in column then subtract number til 0 from column

  1. #1
    Forum Contributor
    Join Date
    06-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    102

    find max in column then subtract number til 0 from column

    spreadsheet.gif

    i need to write a macro that take values from B2:B8

    and subtracts the value from the max value in E1:E12 if the value hits zero and some value is remaining then it would go to the next max value

    the amount subtracted from each value would need to show up in cell G with the remaining amount from E showing up in H

    for instance I'd take credit cards value of 2476.21 and subtract it from soft goods then the remaining from gifts, jewerly then textbooks....until the amount is 0 in column B2 for credit cards.

    The macro would go through all values in B2:B8 in that same manner where it subtracts the amounts out of E1:E12.



    If anyone could help me figure this out i'd really appreciate it.
    Last edited by carden2; 06-20-2012 at 01:54 PM.

  2. #2
    Forum Contributor
    Join Date
    06-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find max in column then subtract number til 0 from column

    anyone have any ideas?

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: find max in column then subtract number til 0 from column

    carden2,

    Your test workbook conflicts with the information in your original post. Your test workbook doesn't have any info in column C or F. Also, the largest value is Cash Deposit (A3) which is 4396.14 (B3), but that isn't mentioned. Also, if you're taking a value and subtracting items from it until it equals 0, then wouldn't your result just be 0?

    Perhaps you can have a before and after worksheet to show what you want the macro to accomplish. And include instructions in the the form of step by step logic that someone doing it manually would use.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    06-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find max in column then subtract number til 0 from column

    I edited my post with a picture of my spreadsheet hope this clears up the confusion.
    Last edited by carden2; 06-19-2012 at 04:46 PM.

  5. #5
    Forum Contributor
    Join Date
    06-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: find max in column then subtract number til 0 from column

    anyone have any ideas?

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: find max in column then subtract number til 0 from column

    carden2,

    Now you've replaced your workbook with a picture. Generally speaking, a test workbook is always preferred so that we don't have to recreate your sample data by hand. You also ignored both pieces of my advice to 1, provide a before and after scenario so that we could see the expected results, and 2, provide step by step logic that someone doing this manually would follow.

    Why is Cash Deposit not included?
    How come your Totals field in column C is 7083.31 (the sum of B1:B4) when you have data in B7 and B8?
    How come your Totals field in column F is 3904.62 (the sum of E11:E18) when you have data in E1:E8 and also E10?


    You state:
    Quote Originally Posted by carden2 View Post
    the amount subtracted from each value would need to show up in cell G with the remaining amount from E showing up in H

    for instance I'd take credit cards value of 2476.21 and subtract it from soft goods then the remaining from gifts, jewerly then textbooks....until the amount is 0 in column B2 for credit cards.
    So in G1 you would want 2476.21 and in H1 you would want 2476.21-1272.71 = 1203.5, is that correct?
    Then in G2 would again be 2476.21, or the new 1203.5 value? and in H1 would be 1203.5-458.46 = 745.04, is that correct?

    That starts to make sense, but this confuses me:
    Quote Originally Posted by carden2 View Post
    subtracts the value from the max value in E1:E12
    The max value in E1:E12 is Caps/gowns at 3526.82, so why did we start at Soft Goods which is only 1272.71??

    There seem to be some gaps in logic that you've skipped over. A macro is nothing but a logic bot that executes a specific set of instructions in a specific order, hence the request for step by step logic that someone performing this task manually would follow.

+ 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