Help with a formula?

[nicholssvcco]

I would like to set up a worksheet that will calculate based on the
following scale.

if an amount was in a specific range than the corisponding multiplier
would be used.


Example:
if part (a) costs .79 then multiply by 5.75 to come up with sale
price.




$0.00 - $0.49 6


$0.50 - $0.99 5.75


$1.00 - $1.49 5.5


$1.50 - $1.99 5.25


$2.00 - $2.49 5


Could anyone help me?

Jamey

 

[swatsp0p]

set up a table with two columns as such:

---R---|--S---
$0.00 | 6
$0.50 | 5.75
$1.00 | 5.5
$1.50 | 5.25
$2.00 | 5

Then enter a VLOOKUP formula as such:

=VLOOKUP(P13,R2:S6,2,1)*P13 (of course, adjust ranges to meet your
needs)

I am curious as to what you want to happen to amounts greater than
2.49? If the multiplier of 5 is valid for all amounts greater than
2.00 you are all set.

Does this work for you?

 

[nicholssvcco]

swatsp0p

well the numbers continue to go up as the multiplier drops.

I have a service company,and when it comes to pricing my parts I hav
this multiplier table I use. I would like to plug in the cost in on
box and it would give me the multiplied price in another box.

I don't think the formula you mentioned will work, or else I don'
understand it.

I can provide you the complete list of multipliers if you need it t
see the whole picture.

Jame

 

[swatsp0p]

Sure, you can post the entire range if you like. However, VLOOKUP
simply looks for the stated value (located in P13 in my example) and
returns the corresponding value in the second column in your lookup
table (R2:S6 in my example). If an EXACT match is not found, it
returns the next largest value that is less than the lookup value.
Hence, if the value in P13 is 1.99, it reverts to 1.50 and returns
5.25. In the formula, this value is then multiplied by the value in
P13 to come up with your selling price.

Now, all you need to do is to enlarge your table with each of your
'break points' and their corresponding value (this data table must be
sorted 'ascending' by the first column of data). Then modify the
formula to include that expanded range, as such:

=VLOOKUP(P13,$R$2:$S$26,2,1)*P13 (note I added $ to the table
reference to fix the range so you can copy the formula down a range).

Does this help you more?

 

[Brian]

If the costs is always broken into $0.50 increments and the rate increment is
always $0.25, then you can use the following formula:
=6-ROUNDDOWN(Cost/0.50,0)*0.25