Splitting Numbers
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
|