if then formula

  • Thread starter Thread starter learningaccess
  • Start date Start date
L

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

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
 
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
 
Back
Top