DISPLAY MATH RESULTS IN A DIFFERENT CELL

[GMan62]

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

 

[Qroozn]

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?

 

[Yogi Anand]

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!

 

[Jay Petrulis]

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

 

[Yogi Anand]

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

Regards!