Using IF and MIN

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
=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.
 
=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)
 
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)
 
Back
Top