+ Reply to Thread
Results 1 to 5 of 5

Auto calculate totals and sums based on variable numbers

  1. #1
    Registered User
    Join Date
    05-12-2015
    Location
    Adelaide Australia
    MS-Off Ver
    various
    Posts
    15

    Auto calculate totals and sums based on variable numbers

    Hi,
    I'm trying to auto calculate something based on this:


    Amount Inside No Fees
    14.00 or less 1 (= amount minus inside fee minus 3)
    14.01 to 20.00 2 (= amount minus inside fee minus 3)
    20.01 to 24.00 2.5 (= amount minus inside fee minus 3)
    24.01 to 50.00 2.9 (= amount minus inside fee minus 3)
    50.01 and above 3.4 (= amount minus inside fee minus 3)

    So that if i put a number in the left column between one of those value it autocalculates the other values? Is that easy?

    Cheers,

    Benn
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Auto calculate totals and sums based on variable numbers

    1st, change what you have in column C, to what I have shown in column B...
    B
    C
    D
    E
    3
    Amount Inside No Fees
    4
    0
    14.00 or less
    1
    (= amount minus inside fee minus 3)
    5
    14.01
    14.01 to 20.00
    2
    (= amount minus inside fee minus 3)
    6
    20.01
    20.01 to 24.00
    2.5
    (= amount minus inside fee minus 3)
    7
    24.01
    24.01 to 50.00
    2.9
    (= amount minus inside fee minus 3)
    8
    50.01
    50.01 and above
    3.4
    (= amount minus inside fee minus 3)

    Then use this in H11, copied down...
    =IF(G11="","",VLOOKUP(G11,$B$4:$D$8,3,1))
    The "No Fee" calc would be...
    =G11-H11-3
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Auto calculate totals and sums based on variable numbers

    you can use lookup
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    05-12-2015
    Location
    Adelaide Australia
    MS-Off Ver
    various
    Posts
    15

    Re: Auto calculate totals and sums based on variable numbers

    Hey guys, that's awesome. Thank you soooo much

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Auto calculate totals and sums based on variable numbers

    Happy to help

+ 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. Calculate multiple cells sums based upon another cell
    By swiftworks in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 11:44 AM
  2. [SOLVED] Calculate totals within and based on information in 3 worksheets
    By NewbieOfVBA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2012, 04:40 PM
  3. Calculate Commission based on running totals
    By SebastienSoum in forum Excel General
    Replies: 5
    Last Post: 12-21-2011, 01:55 PM
  4. How do i calculate totals based on yes/no lists?
    By kingchunk in forum Excel General
    Replies: 3
    Last Post: 05-04-2010, 03:37 PM
  5. Calculate totals based on date
    By shane24 in forum Excel General
    Replies: 4
    Last Post: 01-04-2007, 03:29 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