Creating value ranges to calculate cost markup.

[joshbjames]

I have a sheet of 30,000 parts with my vendor costs and I'd like to figure out a formula to apply a certain markup percentage for a certain range of values.

$.01-$.49 : Markup Percent 809% ( or mark-up multiplier= 9.091) ex. $.30*9.091=$2.73
$.50-$1.99 : 567% (6.667)
$2-$24 : 212.50% (3.12)
$25-$99 : 177.78% (2.77)
$100-$499 : 132.56% (2.326)
$500-$1,999 : 78.57% (1.786)
$2,000+ : 66.67% (1.667)

Is there a formula that will determine which bracket my cost belongs and multiplies it by the correct value?

 

[Oaktree]

Sure.

Either VLOOKUP, where the array structure is {1st group lower bound, factor; 2nd group lower bound, factor; ...} as in:

=ROUND(A1*VLOOKUP(A1,{0,9.091;0.5,6.667;2,3.12;25,2.77;100,2.326;500,1.786;2000,1.667},2),2)

or LOOKUP, where the first array has the lower bounds and the second array has the factors.

=ROUND(A2*LOOKUP(A2,{0,0.5,2,25,100,500,2000},{9.091,6.667,3.12,2.77,2.326,1.786,1.667}),2)

edit: typo