If a positive number, then multiple with another cell, if.

[BooInc]

Hi all,
I'm trying to work out this formula.

A

B

C

D

16.14

-21

75

-338.94

26.61

18

14

478.98

So cell D currently is =sum(A*B) but if it is a negative number, I just 0 the cell.
I want it so if cell B is a negative number, then cell D = 0, if it is a positive number, then =A*B.

I've tried with something like this, =IF(B1<0,$A1*B1, 0) but that just equals to -338.94.

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

22 11 03.xlsm
	A	B	C	D
1
2	16.14	-21	75	0
3	26.61	18	14	478.98
Boolnc

Cell Formulas
Range	Formula
D2:D3	D2	=MAX(A2*B2,0)

BTW, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

 

[Peter_SSs]

I've tried with something like this, =IF(B1<0,$A1*B1, 0) but that just equals to -338.94

That idea would work too, but you have the two results the wrong way around.

22 11 03.xlsm
	A	B	C	D
1	16.14	-21	75	0
2	26.61	18	14	478.98
Boolnc

Cell Formulas
Range	Formula
D1:D2	D1	=IF(B1<0,0,$A1*B1)

 

[BooInc]

Welcome to the MrExcel board!

Try this

22 11 03.xlsm
	A	B	C	D
1
2	16.14	-21	75	0
3	26.61	18	14	478.98
Boolnc

Cell Formulas
Range	Formula
D2:D3	D2	=MAX(A2*B2,0)

BTW, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Thank you for the fast reply Peter!
It worked as I needed it too.

And I'll try the XL2BB next time I need help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Peter_SSs]

I note that you have quoted post #2 & said that worked but marked post #3 as the solution. That is fine, but note that the post #3 formula will only be reliable if column A is always positive and it is only column B that can switch between positive and negative.

 

[BooInc]

I note that you have quoted post #2 & said that worked but marked post #3 as the solution. That is fine, but note that the post #3 formula will only be reliable if column A is always positive and it is only column B that can switch between positive and negative.

I did noticed the 2nd post after I replied. I'm going to use post #2. I haven't used the MAX function before, I hope I can use it more.

 

[Peter_SSs]

I'm going to use post #2. I haven't used the MAX function before, I hope I can use it more.

Cheers. Thanks for clarifying.