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

 

[Hammerjoe]

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.

Your formula works. Thank you for the help.

This is part of an already long formula in my sheet and wanted to keep it as simple as possible because it does alot of checks like that.

 

[Rick Rothstein]

Try

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

Originally I was going to give the OP that formula, but then figured if he did not want to use simple IF function calls, he probably was looking to remove all function calls. Your formula is identical to mine except that I add the two logical expressions together instead of using the equivalent OR function on them.

 

[Hammerjoe]

Thank you so much all for the help.

It works great.

 

[Marcelo Branco]

Originally I was going to give the OP that formula, but then figured if he did not want to use simple IF function calls, he probably was looking to remove all function calls. Your formula is identical to mine except that I add the two logical expressions together instead of using the equivalent OR function on them.

Rick,

It seems your formula (post 8) returns 2*A5 when both conditions are met.

M.

 

[Rick Rothstein]

Rick,

It seems your formula (post 8) returns 2*A5 when both conditions are met.

Good point! How about this then...

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

 

[Marcelo Branco]

Good point! How about this then...

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

According to the formula of post 1
IF(A5<=$A$1,A5,IF($b$1=2,0,A5))

i think it should be
=A5*((A5<=$A$1)+($B$1<>2)>0)

M.

 

[Rick Rothstein]

According to the formula of post 1
IF(A5<=$A$1,A5,IF($b$1=2,0,A5))

i think it should be
=A5*((A5<=$A$1)+($B$1<>2)>0)

Sloppy editing on my part. Of course, my question from Message #7 has not been addressed by the OP yet (hmm, I just noticed I have been omitting the equal sign part of the comparison for quite some time now).