Using IF and MIN

[Guest]

I am working on a spreadsheet with the following parameters:

D E F G H
MIN 45k 100k 500k 1000k
$95.00 $2.57 $2.68 $2.62 $2.57

Cell B22 is the weight in kilograms (k).
I need to write a formula to calculate a rate based on the above. I have
the following written, but it multiplies anything under 45k by $95.00 when
$95.00 should be a flat MINIMUM charge. Can anyone help?

=(IF(B22<45,D19,IF(B22<100,E19, IF(B22<500,F19,IF(B22<1000,G19,H19)))))*B22

 

[Guest]

=IF(B22<45,D19,IF(B22<100,E19*B22,IF(B22<500,F19*B22,IF(B22<1000,G19*B22,H19*B22))))

There may be a better way of doing this (probably my manuevering brackets)
but if you move your multiplier into the equation rather than at the end it
should give you what you want. You'll have to test it out.

 

[Guest]

You are absolutely WONDERFUL!! Thank you for taking the time to help me

 

[Bob Phillips]

=IF(B22<45,D19,IF(D19,IF(B22<100,E19,IF(B22<500,F19,IF(B22<1000,G19,H19))))*B22)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

or even

=MAX(95,LOOKUP(B22,{0,100,500,1000},E19:H19)*B22)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)