| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
