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

Jambruins wrote:
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

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.

"Jambruins" wrote:

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

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/L30,AVERAGE(J3,L3),IF(AVERAGE(J3,L3)=0,AVERAGE(J 3,L3)+100,AVERAGE(J3,L3)-100))

"Jambruins" wrote:

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

Jambruins wrote:

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