I am trying to figure out how to do something pretty complicated. I have a
row with four columns. At any time the columns may have a positive or
negative number. I would like to take the negative numbers, add them
together, split get their average, and add the average to the positive
numbers.

For example, this time the numbers are 162, -85, -95 and 198. I know the
average of the negatives is -90. I would like to add this -90 to the 162 and
198 to get 72 and 108 respectively. The final result I would like to see is
72, 0, 0, 180 (I already know how to make a negative number show 0)

But in the future, if the numbers are... say, 162, -30, -50 and -25, I would
like it to add all those negatives to the 162 to get 57, 0, 0, 0.

Likewise, if the numbers end up being 162, 130, -57 and 32, I'd like to
split the -58 by three and add them to the others, yielding 143, 111, 0, 13.

Is there any way to make Excel do this for me?

Thank you.

I put my data in columns A:D, then I inserted 4 more columns (E:H) and put this
in E1 and dragged across to H1:

=IF(A1<0,0,A1+SUMIF($A1:$D1,"<"&0)/MAX(1,COUNTIF($A1:$D1,""&0)))

Then I selected E1:H1 and dragged down as far as my data went.

It seemed to work ok with your test data.


Xaenyth wrote:

I am trying to figure out how to do something pretty complicated. I have a
row with four columns. At any time the columns may have a positive or
negative number. I would like to take the negative numbers, add them
together, split get their average, and add the average to the positive
numbers.

For example, this time the numbers are 162, -85, -95 and 198. I know the
average of the negatives is -90. I would like to add this -90 to the 162 and
198 to get 72 and 108 respectively. The final result I would like to see is
72, 0, 0, 180 (I already know how to make a negative number show 0)

But in the future, if the numbers are... say, 162, -30, -50 and -25, I would
like it to add all those negatives to the 162 to get 57, 0, 0, 0.

Likewise, if the numbers end up being 162, 130, -57 and 32, I'd like to
split the -58 by three and add them to the others, yielding 143, 111, 0, 13.

Is there any way to make Excel do this for me?

Thank you.

--

Dave Peterson

Thank you, Dave! This is exactly what I needed. You rock!

"Dave Peterson" wrote:

I put my data in columns A:D, then I inserted 4 more columns (E:H) and put this
in E1 and dragged across to H1:

=IF(A1<0,0,A1+SUMIF($A1:$D1,"<"&0)/MAX(1,COUNTIF($A1:$D1,""&0)))

Then I selected E1:H1 and dragged down as far as my data went.

It seemed to work ok with your test data.


Xaenyth wrote:

I am trying to figure out how to do something pretty complicated. I have a
row with four columns. At any time the columns may have a positive or
negative number. I would like to take the negative numbers, add them
together, split get their average, and add the average to the positive
numbers.

For example, this time the numbers are 162, -85, -95 and 198. I know the
average of the negatives is -90. I would like to add this -90 to the 162 and
198 to get 72 and 108 respectively. The final result I would like to see is
72, 0, 0, 180 (I already know how to make a negative number show 0)

But in the future, if the numbers are... say, 162, -30, -50 and -25, I would
like it to add all those negatives to the 162 to get 57, 0, 0, 0.

Likewise, if the numbers end up being 162, 130, -57 and 32, I'd like to
split the -58 by three and add them to the others, yielding 143, 111, 0, 13.

Is there any way to make Excel do this for me?

Thank you.

--

Dave Peterson