Finding middle value

[Jambruins]

I have numbers in cells J3:J61 and L3:L61. Some are positive and some are
negative. I would like cells K3:K61 to find the middle value between the
corresponding J and L cells. For example
cell J61 is -185
cell L61 is +113
I would like cell K61 to be +136.

Once a positive number goes down to +100 it would then go to -101 instead of
99. How would I do this? Thanks

 

[Guest]

I have numbers in cells J3:J61 and L3:L61. Some are positive and some are
negative. I would like cells K3:K61 to find the middle value between the
corresponding J and L cells. For example
cell J61 is -185
cell L61 is +113
I would like cell K61 to be +136.

Once a positive number goes down to +100 it would then go to -101 instead of
99. How would I do this? Thanks

---------------

Can you explain your example a little more? I don't understand in what sense
+136 is in the "middle" between +113 and -185. What's the math you use to
arrive at +136?

If I average =(+113-185)/2 I get -36. Somehow you then want to apply a rule to
transform -36 to +136?

Bill

 

[bj]

I think the example should have resulted in -136 instead of +136 if this is
the case try
=if(J61+L61<0,-100,100) + (if (J1<0,J1+100,J1-100)+if(L1<0,L1+100,L1-100))/2

if you meant for it to be +136, try putting a parenthsis around the above
fomula and putting a minus sign in front of it.

 

[B. R.Ramachandran]

I think the answer in your example should have been -136 (as bj noted in his
reply);
the following formula (in cell K3) is a possibility
=IF(J3/L3>0,AVERAGE(J3,L3),IF(AVERAGE(J3,L3)>=0,AVERAGE(J3,L3)+100,AVERAGE(J3,L3)-100))

 

[Jerry W. Lewis]

I have numbers in cells J3:J61 and L3:L61. Some are positive and some are
negative. I would like cells K3:K61 to find the middle value between the
corresponding J and L cells. For example
cell J61 is -185
cell L61 is +113
I would like cell K61 to be +136.

Once a positive number goes down to +100 it would then go to -101 instead of
99. How would I do this? Thanks