Using IF and MIN

T

Teri

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
 
T

tim m

=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.
 
B

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)
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top