Thanks:  0
Likes:  0

# Thread: DISPLAY MATH RESULTS IN A DIFFERENT CELL

1. I'm currently using a spreadsheet that subtracts numbers and comes up with a positive or negative number as the results. Once the calculation is completed, I want the result to be displayed in a different cell. I have included 2 examples of my spreadsheet to better illustrate what I mean.

In this example AUTH (61) minus ONHAND (63) = exce/short (2). I am in excess of my allowance by 2 and I want the excess number to be displayed in the EXCESS column and a 0 in the SHORT column. If the answer is 0, then I want a 0 in both the EXCESS and the SHORT cells.

1 BIN # AUTH ONHAND exce/short EXCESS SHORT
2 09517 61 63 2 2 0
3 32197 4 4 0 0 0

In this example AUTH (198) minus ONHAND (177) = exce/short (-21). I am short of my allowance by 21 and I want the exce/short number to be displayed in the SHORT column and a 0 in the EXCESS column.

BIN # AUTH ONHAND exce/short EXCESS SHORT
27 44312 198 177 -21 0 -21

In both examples I have to manually type the exce/short number in the appropriate EXCESS and SHORT columns, and I have over 200 rows of data I have to manually type the result in. Is there a way for excel to automatically display the data in the appropriate column and a 0 in the other column?

Any assistance you can provide will be greatly appreciated.

Thanks,

Gary Edgar

2. say the exce/short=col3
excess=col4
short=col5

now a4=if(a3<0,a3,0)
and a5=if(a3>0,a3,0)

does this do what you want?

3. If your entries laid out as
A1 ... BinNumber
B1 ... AUTH
C1 ... ONHAND
D1 ... exc/short
E1 ... excess
F1 ... short

then formula in D2 ... =C2-B2
then formula in E2 ... =IF(\$D2>0,\$D2,0)
then formula in F2 ... =IF(\$D2<0,\$D2,0)

Hope This Helps!

4. On 2002-05-08 20:33, Yogi Anand wrote:
If your entries laid out as
A1 ... BinNumber
B1 ... AUTH
C1 ... ONHAND
D1 ... exc/short
E1 ... excess
F1 ... short

then formula in D2 ... =C2-B2
then formula in E2 ... =IF(\$D2>0,\$D2,0)
then formula in F2 ... =IF(\$D2<0,\$D2,0)

Hope This Helps!
With Yogi's setup, you can also try the following:

E2: =MAX(C2-B2,0)
F2: =MIN(C2-B2,0)

Frees up one column, too.

Bye,
Jay

5. Thanks Jay!
Your suggestion also avoids using the IF function ... this way we will save that big gun for bigger game.

Regards!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•