Using IF and MIN
 

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

Using IF and MIN
 

=IF(B22<45,D19,IF(B22<100,E19*B22,IF(B22<500,F19*B 22,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.

"Teri" wrote:

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

Using IF and MIN
 

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

"tim m" wrote:

=IF(B22<45,D19,IF(B22<100,E19*B22,IF(B22<500,F19*B 22,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.

"Teri" wrote:

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

Using IF and MIN
 

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

--
HTH

Bob

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

"Teri" wrote in message
...
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

Using IF and MIN
 

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)

"Teri" wrote in message
...
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