| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex Formula
Please tell me how to write this formula.
=IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
What do you want the formula to return if F4 is not "Y"?
=IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No") -- Regards, Peo Sjoblom "marwildfw" wrote in message ... Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
Hi,
The following will leave the cell blank if all of the conditions aren't met. =if(AND(f4="Y"),(RATE*O4)=150,(RATE*O4)<=275,(RAT E*O4),"") Let me know if this works for you. Thanks, Peggy "marwildfw" wrote: Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
0.00 if not "Y"
"Peo Sjoblom" wrote: What do you want the formula to return if F4 is not "Y"? =IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No") -- Regards, Peo Sjoblom "marwildfw" wrote in message ... Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
=IF(F4="Y",MAX(MIN(RATE*O4,275),150),0)
-- Regards, Peo Sjoblom "marwildfw" wrote in message ... 0.00 if not "Y" "Peo Sjoblom" wrote: What do you want the formula to return if F4 is not "Y"? =IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No") -- Regards, Peo Sjoblom "marwildfw" wrote in message ... Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
Actually, if F4 ="N" the result should be the greater of (rate*O4) or 150
"Peo Sjoblom" wrote: What do you want the formula to return if F4 is not "Y"? =IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No") -- Regards, Peo Sjoblom "marwildfw" wrote in message ... Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex Formula
Actually, if F4 ="N" the result should be the greater of (rate*O4) or 150
"Peo Sjoblom" wrote: What do you want the formula to return if F4 is not "Y"? =IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No") "marwildfw" wrote: Please tell me how to write this formula. =IF(E4="H",0,IF(F4="Y",275,(RATE*O4))) The above statement works fine but it gives me the worng answer. The maximum amount cannot exceed 275 and the minimum cannot be less than 150. The portion of the formula beginning at F4 really needs to say: IF F4=Y,(RATE*O4),BUT NOT LESS THAN 150 OR MORE THAN 275 In english, the cell occupied by this formula should equal the sum of (RATE*O4) but the result cannot be less than 150 or more than 275. Can anyone helP? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| complex formula | Excel Discussion (Misc queries) | |||
| Complex Formula | Excel Worksheet Functions | |||
| Can someone help with this complex formula? | Excel Worksheet Functions | |||
| I Need Help with complex formula ? | Excel Worksheet Functions | |||
| complex formula | Excel Worksheet Functions | |||
Linear Mode
