Formulas using two cells

[Mark]

I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if
the text in column E is set to the value of "H". If the value in column E is
<> H then I want the formula in column G to be =MAX(MIN(210-E2,60),0).

How do I have this conditional formula in the same cell?

 

[Lars-Åke Aspelin]

I have a formula in column G =MAX(MIN(220-F2)) that I want to set only if
the text in column E is set to the value of "H". If the value in column E is
<> H then I want the formula in column G to be =MAX(MIN(210-E2,60),0).

How do I have this conditional formula in the same cell?

Try this:

=IF(G2="H", your first formula here , your second formula here )

Your first formula looks a bit odd. Have you mistyped it?
If it really is
=MAX(MIN(220-F2))
you can simplify it to just
=220-F2

Hope this helps / Lars-Åke

 

[Mark]

This is what I have now =IF(E3="H", 220-F3, MAX(MIN(210-E3,60),0) )

When E3 = H then the formula works fine, when it is not H then I get #Value!
in the cell.

 

[Rick Rothstein]

Your first formula doesn't make much sense as there is only a single argument meaning it is the same as 220-F2. Given that, try putting this in G2 (and copying down as needed)...

=IF(E2="H",220-F2,MAX(MIN(210-E2,60),0))

 

[Jacob Skaria]

When you have 210-E3; the formula expects a numeric in E3..It returns a
#value error because E3 is not a numeric..

If this post helps click Yes

 

[Lars-Åke Aspelin]

And what do you have in E3 when the problem occurs?
If you dont have a number there, or something that can be interpreted
as a number, Excel will have problems to calculate 210-E3.

Lars-Åke

 

[Rick Rothstein]

That is because you MAX(MIN(210-E3,60),0)) expression is trying to do a mathematical operation on text (210 minus the contents of E3). Are you sure you want E3 to be subtracted from 210 or did you mean to write F3 (like you have in the first argument of the IF function?

 

[Mark]

That was it... it should have been 210-F3 and not E3... Thank you both for
the assistance.

 

[Dana DeLouis]

=IF(E3="H", 220-F3, MAX(MIN(210-F3,60),0) )


Hi. Instead of MAX(MIN(210-F3,60),0)

I like to use:

=MEDIAN(0,210-F3,60)

= = = = = = =
HTH
Dana DeLouis