+ Reply to Thread
Results 1 to 14 of 14

Formula needed! Please Help!

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Formula needed! Please Help!

    Not sure if this is possible. I am looking for a formula that if I input a number in cell B4 the cell below will automatically do the calculations below based on the size of the number in B4. For instance if I input 175,000 in B4 the formula will take 175 * 4.25 + 1,112.50 + 4.50 * 175 + 87.50


    150,000 - 250,000 Mulitply by $4.25 per thousand and add $1,112.50 to total + Multiply by $4.50 per thousand and add $87.50 to total
    250,000 - 500,000 Multiply by $3.50 per thousand and add $1,300.00 to total + Multiply by $3.50 per thousand and add $37.50 to total
    500,000 - 10,000,000 Multiply by $3.25 per thousand and add $1,425.00 to total + Multiply by $2.75 per thousand and add $12.50 to total
    Last edited by mmiller12; 05-17-2013 at 12:23 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Formula needed! Please Help!

    What's the calculation for values entered that's below 150,000 or above 10 million?

    Also, if it's 250,000 is the calculation suppose to be the 1st or the 2nd?

  3. #3
    DaveDeV
    Guest

    Re: Formula needed! Please Help!

    Hi MM,

    This is a case where a lookup table is better than gazillions of IF statements.

    Table should contain 5 columns:

    1) Lookup Value (0, 150000, 250000,500000)
    2) First factor (0, $0.00425, $0.00350, $0.00325) - these have all been divided by 1000
    3) Second factor (0, $1112.50, $1300.00, $1425.00) these to be added
    4) Third Factor (0, $0.00450, $0.00350, $0.00275) - also divided by 1000
    5) Fourth Factor (0, $87.50, $37.50, $12.50) these to be added

    Name the table as "Factor_Table" and use the following formula to retrieve the factors for a given value (Amount)

    =LOOKUP(Amount, Factor_Table, 2) - this will return the appropriate value for Factor 1
    =LOOKUP(Amount, Factor_Table, 3) - this will return the appropriate value for Factor 2
    etc.

    I hope that helps

    Dave

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula needed! Please Help!

    dluhut that is good observation and a valid question. Its unlikely that it will ever be 150,000 or below but so something will populate lets say the first range is 0-250,000 the second is 250,001 - 500,000 and the last 500,001 and up.

  5. #5
    DaveDeV
    Guest

    Re: Formula needed! Please Help!

    Hi MM,

    Attached is a sample of the lookup table plus the worked formula for the cost calculations.

    Have fun,

    Dave

    Compound Stepped Tariffs.xlsx

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula needed! Please Help!

    Thanks Dave I would prefer a formula for a single cell if it is possible that does not require there to be a table in the document.
    Last edited by mmiller12; 05-16-2013 at 04:08 PM.

  7. #7
    DaveDeV
    Guest

    Re: Formula needed! Please Help!

    Hi MM,

    Create the lookup table somewhere hidden in the workbook and name it as suggested.
    In your "single cell" create a formula that combines the formulae in the "worked example" into a single formula and you have what you want.

    If you're still stuck this time tomorrow, I'll do it for you (it's after 22h00 and I need my beauty sleep)

    Ciao

    Dave

  8. #8
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Formula needed! Please Help!

    Please Login or Register  to view this content.
    Assuming you input on cell B4

  9. #9
    DaveDeV
    Guest

    Re: Formula needed! Please Help!

    MM,

    The advantage of using a table in this type of application is that those factors are not likely to be static and could change at least once a year - just far enough apart in time for you to have almost forgotten how and where you buried them in formulae in cells...

    Having a single table used by all instances provides a single place to maintain the factors and usually reduces testing/debugging time.

    But, its your game...

    Dave

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula needed! Please Help!

    Hi and welcome to the forum

    We would love to help you with your question, but 1st, in accordance with forum rule, please rename your thread to something more meaningful, that actually describes your problem.

    Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

    Also, many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertese, they might only open those threads.

    Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title
    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

  11. #11
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula needed! Please Help!

    Dluhut. This is a brilliant formula. It will work if I do the calculations in a new worksheet. However, in my worksheet I am using it will not calculate. They way it is formatted is that you input into B4 and then the output will come out at B19. I originally had B4 merged with C4 and B19 merged with C19 but I thought it might be affecting your formula but it still would not work even after I unmerged them. Also, I do have text in A14 I am not sure if that is affecting it also. Thank you again.

  12. #12
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Formula needed! Please Help!

    Hey mmiller12,

    B4 and B19 is just an example of where the input and output is.

    In the formula, you can change B4 and B19 to any cell you want.

    Also, as to my knowledge, even though B4 and C4 is merged, it'll still calculate.

    If you want, you can attach a sample and we can try and help out =)

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula needed! Please Help!

    mmiller and dluhut...
    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc

  14. #14
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Formula needed! Please Help!

    You are awesome. Figured it out and it works great! Thank you so much for your help. And for the Mods I am not sure how to edit the title once posted. I will change the title if you can tell me how.

+ 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