# Percentage calculation based on number entered

1. ## 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. ## 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"))))))

3. ## Re: Percentage calculation based on number entered

Originally Posted by Brewha51
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:
`Please Login or Register  to view this content.`

Hope this helps.

4. ## 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. ## 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

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Percentage calculation based on number entered

Nice solution, bebo

12. ## Re: Percentage calculation based on number entered

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

13. ## Re: Percentage calculation based on number entered

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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