finding the middle cell in a column of numbers

Hi Guys,

Not sure of the right tiltle here goes

I am looking for a formulas to first find the middle number in a column of numbers eg 1,2,3,4,5 3 is the middle (similar to median) thats where the calculations start...

it then assigns values of minus to the numbers above the middle and plus values to the numbers below the middle
1 -50
2 -50
3 0
4 +50
5 +50




now when it comes to even numbers eg 1,2,3,4,5,6 if i use median it divide 3 & 4 and comes up with 3.5 ........ i want it to recognize 3 and 4 as the middle numbers
and assign plus and minuses above and below the middle numbers
1 -50
2 -50
3 -25
4 +25
5 +50
6 + 50

I do hope you all understand, this has been buggin me for weeks

cheers in advance

Bump
Bump
Bump

Bump

See if this is what you want.

A5:A10 = numbers
B5 formula, copied down: =IF(A5<=ROUNDUP(COUNT($A$5:$A10)/2,1),"-"&A5,"+"&A5)

See attached.

Hi Guys

thanks for the replys, although I dont really understand what they mean!

To add to your confusion, a third approach.

Originally Posted by WHER

To add to your confusion, a third approach.

WHER, looks like i have found another genius.

first look; looks like its the ticket, that's what i have been after..........

I will look into it further and get back to you

cheers
stephen

Originally Posted by martindwilson

how about
=IF(A1="","",IF(ISODD(COUNTA($A$1:$A$10)),IF(A1<MEDIAN($A$1:$A$10),-50,IF(A1=MEDIAN($A$1:$A$10),0,50)),IF(A1=MEDIAN($A$1:$A$10)-0.5,-25,IF(A1=MEDIAN($A$1:$A$10)+0.5,25,IF(A1<MEDIAN($A$1:$A$10),-50,50)))))

Hi Martin, thanks for the formula, I have tried it but could not get it to work

maybe (probably) doing something wrong

Cheers

Hi Martin

I see what it does but when i apply it to the main sheet it loses something

Hi Stephen,
I applied Martin's formula to your workbook, have a look.

Originally Posted by WHER

Hi Stephen,
I applied Martin's formula to your workbook, have a look.

Hey WHER, I applied it to a different workbook it worked, but when I apply the original it does not work....

I applied what you gave me the other day, but put the even formula into the odd formula I am looking to combine them but one cell wont play ball

D79

If you have time to look, would be great.....

cheers

The formula in D77:D89 seems to do the trick for both even and uneven "cases", but so does Martin's formula.

Hey sorry guys

we had a small fire in the house friday evening, been a bit busy clearing up..


I will look again at what you guys have posted and reply...