Complex Formula

M

marwildfw

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?
 
P

Peo Sjoblom

What do you want the formula to return if F4 is not "Y"?

=IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No")
 
P

pshepard

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,(RATE*O4),"")

Let me know if this works for you.

Thanks,
Peggy
 
M

marwildfw

Actually, if F4 ="N" the result should be the greater of (rate*O4) or 150

:

What do you want the formula to return if F4 is not "Y"?

=IF(F4="Y",MAX(MIN(RATE*O4,275),150),"No")
 

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

Coomplete the formula 1
Use TODAY() function as the criteria in a SUMIF formula 1
return multiple values when within range (greater than and less than) 11
Conditional formatting for time 2
Conditional formatting referencing multiple cells. 3
Help, help! desparate for formula help!! 3
How do I complete this Formula 1
Need something other than IF function 4

Top