logical formula without using =IF

[Hammerjoe]

I am trying to create a formula in excel 2007 without using =IF and I am struggling to come up with a solution.

Cell A1 is the input cell and cell b1 is another input cell.

I am creating a table from A5 to a50 starting with A5=1

The formula I am looking for in row B1:B50 is equal to cell A if cell A<=$A$1 and if($b$1=2) otherwise it equals zero.

I got the formula below but I would like to simplify it without using the IF condition:

IF(A5<=$A$1,A5,IF($b$1=2,0,A5))


Thank you

 

[Dave Patton]

=(a5<=$a$1)*($b$1=2)*a5

 

[Hammerjoe]

=(a5<=$a$1)*($b$1=2)*a5

Thank you for the help Dave, but thats not quite right because if B1=2 then it works but if its not it will zero all rows, but I still want to get the number from A up to and equal to A1.

 

[Dave Patton]

You did state
"The formula I am looking for in row B1:B50 is equal to cell A if cell A<=$A$1 and if($b$1=2) otherwise it equals zero."

 

[Dave Patton]

=(a5<=$a$1)*a5

 

[Marcelo Branco]

I got the formula below but I would like to simplify it without using the IF condition:

IF(A5<=$A$1,A5,IF($b$1=2,0,A5))

I do not know why you do not want to use the IF function.
Although it's more complicated, in my opinion, see if this does what you want

=A5*((A5<=A$1)+(A5>A$1)*(B$1<>2))

M.

 

[Rick Rothstein]

The formula I am looking for in row B1:B50 is equal to cell A if cell A<=$A$1 and if($b$1=2) otherwise it equals zero.

I got the formula below but I would like to simplify it without using the IF condition:

IF(A5<=$A$1,A5,IF($b$1=2,0,A5))

That formula does not do what your said you want in the first sentence above. You formula says to return A if either A<$A$1 or $B$1<>2. So which do you want... what you said in the first sentence or what your formula returns?

 

[Rick Rothstein]

I do not know why you do not want to use the IF function.
Although it's more complicated, in my opinion, see if this does what you want

=A5*((A5<=A$1)+(A5>A$1)*(B$1<>2))

I am not 100% sure the OP's formula is what he actually wants (see Message #7 ). However, if it is, and if I am not mistaken, I think the following does the same thing as it does...

=A5*((A5<$A$1)+($B$1<>2))

By the way, I agree with you.. I cannot see any good reason to not use the IF function (I believe the to IFs are more efficient than the addition and multiplications inherent in our two offerings).

 

[Hammerjoe]

I am sorry for the confusion, I explained myself wrong.
I want to do what if formula does.

 

[Jonmo1]

Try

=OR(A5<$A$1,$B$1<>2)*A5