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.

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")

@ kbkumar you are spot on with the need to that table you could do away with the 2nd column and just have 2 though

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

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.

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

Nice solution, bebo

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