Formula only if numbers are positive.

[PostTIT]

I have a sheet that I'm using to keep track of a few things, not complicated but there's one formula I would like help with to tidy it up. the three websites at the top of my google search yielded nothing so I've come for help because evidently I'm lazy...

To make it really simple I'll only give one set of cells this applies to and then I can apply it to the rest.

Cell A3 has the following formula:

[B]=(A1*B1)+(A2*B2)[/B]

However A1 and A2 are dependant on another formula from other cells (which shouldn't be relevant to my problem). As a result, A1 and A2 could be either negative or positive.

Is there a way to make the given formula in A3 apply only if the value in A1 and A2 is positive? Some sort of IF statement maybe?

Next step would then be to have the formula work for only the positive number, by assuming the negative number is zero (without changing the actual value in the cell - any help on that would be great too.

 

[Tetra201]

Is this what you need?

=MAX(0,A1)*B1+MAX(0,A2)*B2

 

[Rick Rothstein]

Cell A3 [/B]has the following formula:

[B]=(A1*B1)+(A2*B2)[/B]

However A1 and A2 are dependant on another formula from other cells (which shouldn't be relevant to my problem). As a result, A1 and A2 could be either negative or positive.

Is there a way to make the given formula in A3 apply only if the value in A1 and A2 is positive? Some sort of IF statement maybe?

I assume by the highlighted text that you mean both A1 and A2 must be positive at the same time.

=IF(AND(A1>0,A2>0),A1*B1+A2*B2,"")

You did not say what should be displayed if either A1 or A2 (or both) are negative, so I chose to display the empty text string ("").

Next step would then be to have the formula work for only the positive number, by assuming the negative number is zero (without changing the actual value in the cell - any help on that would be great too.

This should do it...

=MAX(0,A1)*B1+MAX(0,A2)*B2

 

[PostTIT]

Yeah that formula works a treat, thanks both.

Just out of interest I found a solution to my second question, and to more cells - found it by the magic of thinking for once... With this formula (If you can get your head around the shifted cells)

=IF(B2 < 0, (B3*C3)+(B4*C4)+(B5*C5), IF( B3 < 0, (B2*C2)+(B4*C4)+(B5*C5),IF(B4 < 0, (B2*C2)+(B3*C3)+(B5*C5),IF(B5 < 0, (B2*C2)+(B3*C3)+(B4*C4),(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)))))

and it could go on i suppose.