How can I get Microsoft excel to calculate increasing number again

[Anna]

I am a small business owner and what I need is to price my inventory. Let's
say my lowest cost ($0.01) will be multiplied by 20%....and my highest cost (
$10.000) will be multiplied by 2%?
Is there a formula to do it and if yes,,,what it is?
Somebody suggested I should use the Conditional Formatting/Formula. Is it
the best for my need and if yes, how do I do it?
Can you give me more details?

Thanks in advance!

 

[Anna]

Thank you for your fast answer.

What I am trying to do is basically to be able to have regressing percentage
against my increasing cost.

My cost is in a column sorted from lowest to highest . I want to be able to
add on the next column a formula with which with one click to apply this
formula to all the cells in the same column (as we do with all formulas).

So,,,if at the starting point of $0.01cost I have in the formula added 20%
[sum= A1*1.20], at the end of my cost list to have only 2% added [
sum=A12510*1.02].


Sample of what I am trying to accomplish:

$50 x 12%
$ 51 x 11.99%
$ 52 x 11.98%
$ 53 x 11.97% and so on...


How do I do it?
Thanks again for your help!

 

[Shane Devenshire]

Hi,

Set up a rate table like
0.01 20%
100 19%
500 18%
....
10,000 2%

suppose this is in the range M1:N20

then use a lookup function

=VLOOKUP(A2,$M$1:$N$20,2,TRUE)*A2

Where A2 contains the cost of an item.