Formulas using two cells

  • Thread starter Thread starter Mark
  • Start date Start date
M

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?
 
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?
Click to expand...

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
 
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.
 
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))
 
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
 
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
 
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?
 
That was it... it should have been 210-F3 and not E3... Thank you both for
the assistance.
 
=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
 
Back
Top