if then formula

[learningaccess]

Hi ...

I'm trying to write a formula in excel

first column has some data with fixed $ values and all other values are -20.
second and third coulumns have $ values.

if column one contains fixed value other than -20 this supercedes col 2 and
3 always.
if column 3 less than column 2 and column one not fixed (=-20) then column
3, else column 2.

ex:

col 1 col 2 col 3
10 (F) 10 8
-20 10
-20 15 12

So I want a column 4 to choose column 1 as answer where col 1 contains fixed
value. if not then choose lesser of 2 and 3 or two in the case where 2 is
the only col w a value.

Please help!!!! Thanks in advance.

 

[Sam Wilson]

Put this in D2 (assuming A1 is the header for column1 and A2 the start of
your data)

=if(A2=-20,min(B2,C2),A2)

 

[rob.allchurch]

Hi ...

I'm trying to write a formula in excel

first column has some data with fixed $ values and all other values are -20.
second and third coulumns have $ values.

if column one contains fixed value other than -20 this supercedes col 2 and
3 always.
if column 3 less than column 2 and column one not fixed (=-20) then column
3, else column 2.

ex:

col 1 col 2 col 3
10 (F) 10 8
-20 10
-20 15 12

So I want a column 4 to choose column 1 as answer where col 1 contains fixed
value. if not then choose lesser of 2 and 3 or two in the case where 2 is
the only col w a value.

Please help!!!! Thanks in advance.

Assuming 'col 1' is in cell A1 ....

In cell "D2" .... =IF(A2<>-20,A2,IF(C2="",B2,IF(C2<B2,C2,B2)))

Try that out and see if it gives the desired results ....

Rob

 

[Sam Wilson]

Rob,

The only time excel will treat empty cells as "0" in a min or max formual is
when all the cells you're taking a max or min over are empty...

I'm not trying to be petty by correcting you, it's just worth knowing,
especially you're pushing the limit on the number of If() statements!

Sam

 

[learningaccess]

Thank you! You really simplified things!