Best-fitting an array

[Parky102!]

Hi. I'm new to forums like this.

In Excel, I have an array of 30 random positive and negative values.

I wish to execute a best-fit via a corresponding cell formula that can be used
to adjust all the values in their individual negative or positive family group, in
one direction away from the absolute maximum deviation accordingly, regardless
of the sign of that maximum value:

Using initially only the highest negative and highest positive value, I have first
subtracted half the absolute difference between them for a value to be the best-fit
adjustment factor.

Typically my adjusted column would look something like below.

0.210 0.220
0.140 < == THESE VALUES TO THESE == > 0.150
0.110 0.120
-0.261 -0.251
0.111 0.121
0.117 0.127
0.279 HIGHEST POSITIVE .279 0.289
-0.024 -0.014
-0.299 HIGHEST NEGATIVE .299 + (highest of the 2 values) -0.289
-0.115 ------ -0.105
0.170 578 / 2 = .289 0.180
0.035 0.045
0.027 .299 - .289 = .010 (best fit adjustment value) 0.037
-0.138 ==== -0.128
0.081 0.091
0.102 0.112
-0.016 -0.006
0.267 0.277

I'm betting this isn't too tricky right?

Cheers

OK my column spacing has gone nuts when posted. Sorry about that.

[Pauleyb]

Best if you attach a workbook with your data and your notes. Ideally, it would also include your expected results (even if they are hand calculated). You're correct, the loss of column spacing is confusing the issue. Maybe this is what you are looking for?
=ABS(MAX($A$1:$A$7)+MIN($A$1:$A$7))/2+A1

This assumes your input is in A1:A7; adjust accordingly

[Parky102!]

Not quite what I was looking for thanks but here's my attached sheet.

Book10.xls

[shg]

Can you explain what rule you used to rearrange column E to column H?

[Pauleyb]

=e7-(max($e$7:$e$24)+min($e$7:$e$24))/2

(and I believe your H23 cell is miscalculated)

[Parky102!]

Thanks Pauleyb. That is the prob resolved. Cheers for that!