How to make a formula in Excel like this...?

Question

I need to know how to create a formula for a table with the following info: I want the cost multipled by the error, with the results being in the loss category if it is negative, and if it is positive, it needs to be in the gain category. Thanks!

PART # COST ERROR LOSS GAIN PART NAME
1026 $44.17 2 $88.34
1087 $2.78 (1) ($2.78)

Sorry- the table got shifted. they all need to be spaced out....

Answer 1

I assume you want to arrive at something like this:

___| A_______| B_______| C_______| D________| E_______|
1__| PART_#_| __COST_| _ERROR_| _LOSS___| __GAIN__|
2__| __1026__| _|_$44.17| ______2__| _________| _$88.34__|
3__| __1087__| ___$2.78| ______(1)_| __($2.78)_| _________|

(note: Use the veritcal lines to help you figure out which column is which)

In column D (the Loss column) in row 2, you can have this conditional formula:
=IF(C2>0,"",B2*C2)
which reads: if Error is less than zero then put in a blank otherwise put in the Cost times the Error

In colum E row 2 you have
=IF(C2>0,B2*C2,"")
which reads: if the Error is greater than zero put in the Cost Times the Error otherwise put in a blank

Hope this helps.

Answer 2

Not certain but you probably need two formulas.

One in the gain column, one in the loss column.

Answer 3

you need to put a "if" statement


put both on loss and gain

=if(f9 >0,"gain","lost")

F9 is your 88.34 or -2.78