+ Reply to Thread
Results 1 to 13 of 13

Percentage calculation based on number entered

  1. #1
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Percentage calculation based on number entered

    Good Morning all!

    Question for the smart people. I have a table of ranges (100 - 1000, 1000 - 2,000, 2,000 - 3,000, 3,000 - 4,000, 4,000 - 5,000, 5,000 - UP) Id like to type a number in cell K5 that multiplies any number typed in K5 that falls between 100 - 1000 to subract that entered number by 45%; 1000 - 2000 to subtract that entered number by 40%; 2000 - 3000 to subtract that number by 35%; 3000 - 4000 to subtract that number by 30%; 4000 - 5000 to subtract that number by 25%; 5000 and above to subtract that number by 20%.

    Hope this makes sense.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,810

    Re: Percentage calculation based on number entered

    you could use a nested if statement
    what version of excel do you have

    =IF(K5>=5000,(K5-(K5*0.2)),IF(K5>=4000,(K5-(K5*0.25)),IF(K5>=3000,(K5-(K5*0.3)),IF(K5>=2000,(K5-(K5*0.35)),IF(K5>=1000,(K5-(K5*0.4)),IF(K5>=100,(K5-(K5*0.45)),"Number less than 100"))))))
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Percentage calculation based on number entered

    Quote Originally Posted by Brewha51 View Post
    Good Morning all!

    .... Id like to type a number in cell K5 that multiplies any number typed in K5 that falls between 100 - 1000 to subract that entered number by 45%; 1000 - 2000 to subtract that entered number by....
    Not sure if you are trying to enter the number in K5 and also wanting to get the results in K5 itself. If so, you would have to look at VBA solutions as you can either enter a value or a formula in a cell but not both.

    In case you meanth to enter the value in cell K5 and get the results in lets say K6, then a simple vlookup formula would be sufficient I suppose.

    Assuing that you creae a table from cell A1:C6 as:

    100 1000 45%
    1000 2000 40%
    2000 3000 35%
    3000 4000 30%
    4000 5000 25%
    5000 20%

    Using cell K5 as the "input" cell (as you mentioned), try the below formula in cell K6:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Hope this helps.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Percentage calculation based on number entered

    Try this with shorter formula with no addition range:
    =IFERROR(K5*LOOKUP(K5,10^2*{1,10,20,30,40,50},1-{0.45,0.4,0.35,0.3,0.25,0.2}),"Invalid number")

  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,389

    Re: Percentage calculation based on number entered

    @ kbkumar you are spot on with the need to that table you could do away with the 2nd column and just have 2 though
    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

  6. #6
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Percentage calculation based on number entered

    Markdown Chart.jpg

    Here's what I have and I hope that it makes sense. The user types in a number in K5 and depending on which range that number falls in, it's % is subtracted accordingly.

  7. #7
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Percentage calculation based on number entered

    Bang on, as always FDibbins. There is no need to have 2nd column - just created in flow

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

    Re: Percentage calculation based on number entered

    brewha, pictures are nice to look at, but impossible to work with, and no-one wants to re-type your data for you.

    Please upload a sample workbook, showing the data you are working with, a few examples of what your expected outcome is, and how you arrived at that
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  9. #9
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Percentage calculation based on number entered

    Markdown chart example.xlsx

    Thanks for the heads-up and sorry for the inital picture. I'm working on a work computer with a Citrix firewall that prevents me from using my "desktop". Anyway, I hope that this makes sense and any help is greatly appreciated

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Percentage calculation based on number entered

    Try this:
    =IFERROR(INDEX(D2:D22,MATCH(K4,B1:B21,-1)),0.2)
    to get %
    or
    =K4*(1-IFERROR(INDEX(D2:D22,MATCH(K4,B1:B21,-1)),0.2))
    to get value after discount

  11. #11
    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,389

    Re: Percentage calculation based on number entered

    Nice solution, bebo

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Percentage calculation based on number entered

    Quote Originally Posted by FDibbins View Post
    Nice solution, bebo
    Thanks FDibbins. In fact it's done before excel file posted
    Anyway, donot drink beer untill getting OP's confirmation

  13. #13
    Registered User
    Join Date
    01-29-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Percentage calculation based on number entered

    +100 to Bebo and everybody else for the help!!!!!!

+ 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