If cell is positive, then multipy, if negative, then take this cell

[adam1993]

I am having trouble finding the right formula to use in excel when it comes to the value showing in a certain cell. I am running into the problem in cell C. For example, cell C_ is positive, I then want cell E_ to equal C_ x D_, if it is negative, I want it to equal D_. (Note: I would like to input this cell into a whole column to continue to do the math for me)

The same issue in reverse which might be the same formula. If cell C_ is negative, then E_ equals D_, but if C_ is positive, then F_ equals D_.

Basically I have either a positive or negative value in cell C, cell D is a dollar amount, cell E is a dollar amount and cell F is a dollar amount as shown below. I am looking for 2 separate formulas (I think). One for Column E and One for Column F that will take into account if C is positive of negative first, if positive then do this, if negative, then do this.

[TABLE="width: 4"]
<tbody>[TR]
[TD]-1.10
[/TD]
[TD]50
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1.27
[/TD]
[TD]50
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1.95
[/TD]
[TD]50
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]-1.52
[/TD]
[TD]50
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

What happens in F if C is negative?

Book1
	C	D	E	F
1	-1.1	50	50
2	1.27	50	63.5	50
3	1.95	50	97.5	50
4	-1.52	50	50
Sheet50

Cell Formulas
Range	Formula
E1	=IF($C1<=0,$D1,$C1*$D1)
F1	=IF($C1<=0,"",$D1)

 

[Legacy 428781]

Assuming that -1.10 is in column A and 50 is in column B, try the following:

Type into cell E1
=IF(C6<0,D6)

Type into cell F1
=IF(C6>=0,C6*D6)

If you want to reduce the number of columns you have, you could delete column F and simply type the following into column E
=IF(C1>=0,C1*D1,D1)
With this method, the cell will display FALSE if the condition is not met.

Then all you have to do is drag the cell's fill handle or double click the fill handle for all the cells in that column to use the formula.