You'll need to switch to:

=MEDIAN(IF(A1:A5,ABS(A1:A5),""))

which must be confirmed with control+shift+enter.

Rehanna wrote:
Hi Biff,
thanks for explaining it and for all your help so far. I worked out what the
problem is, whenever there is a space (ie no data), it puts in a 0. Is there
a part I can put in there so that it doesn't do this? I tried to work in a
IF ....0 part in there but i think i arranged it wrong.

e.g
1.2
1.9
<------- puts zeros in the spaces
1.2
0.5

1.9

Cheers
Rehanna


"Biff" wrote:


Hi!

Based on your sample data (including the negatives):

=MEDIAN(A1:A5) = 0.5

You said you want to calculate the median as if all the values were
positive:

1.2
1.9
1.2
0.5
1.9

=MEDIAN(1.2,1.9,1.2,0.5,1.9) = 1.2

1.2
1.9
1.2-
0.5
1.9-

Array entered:

=MEDIAN(ABS(A1:A5)) = 1.2

The formula I posted is just a way of not having to use an array:

=MEDIAN(INDEX(ABS(A1:A5),,1)) = 1.2

The ABS function converts the values to their absolute values and passes
these values to the INDEX function which in turn passes them to the MEDIAN
function so that this is what you end up with:

=MEDIAN(1.2,1.9,1.2,0.5,1.9) = 1.2

OR, maybe I didn't understand what you were asking for!

Biff

"Rehanna" wrote in message
...

Hi Biff,
This yeided an answer but i removed the minuses from the same data and ran
[=median(A1:A5)] to check it and they weren't the same. Could you break
the
formula down for me so that I understand what every function does?
Cheers
Rehanna

"Biff" wrote:


Hi!

Try this:

=MEDIAN(INDEX(ABS(A1:A5),,1))

Biff

"Rehanna" wrote in message
...

Hi,
I have a spread sheet set up so that some values are negative (this is
so
that they are included in "n", but not included in certain formulas)
and i
am
trying to workout a formula that would calculate the median as if the
values
were positive. As it is a median value, it would not be appropriate to
just
times that answer by -1, and as the -ve values are not in a certain
order,
i
can't use this method [ =MEDIAN(U12*-1,U13*-1,U14*-1 etc....]
Spreadsheet example:
U
1.2
1.9
-1.2
0.5
-1.9
I am not sure if this is even possible, but any help/suggestions would
be
appreciated
Cheers
Rehanna











--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.