Formula help

[Rehanna]

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

 

[Biff]

Hi!

Try this:

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

Biff

 

[Rehanna]

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

Hi!

Try this:

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

Biff

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

 

[Biff]

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

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

Hi!

Try this:

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

Biff

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

 

[Rehanna]

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

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

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

Hi!

Try this:

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

Biff

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

 

[Aladin Akyurek]

You'll need to switch to:

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

which must be confirmed with control+shift+enter.

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


:

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

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

:


Hi!

Try this:

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

Biff


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.

 

[Biff]

Hi!

Median ignores empty cells so I'm assuming your values are the result of
other formulas and you have the display of zeros suppressed.

Try this entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MEDIAN(IF(A1:A7<>0,ABS(A1:A7)))

Biff

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

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

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

:

Hi!

Try this:

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

Biff

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

 

[Rehanna]

Thanks Aladin,
worked a treat!!! =)
Rehanna

You'll need to switch to:

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

which must be confirmed with control+shift+enter.

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


:

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


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

:


Hi!

Try this:

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

Biff


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.

 

[Aladin Akyurek]

Hi!

Median ignores empty cells so I'm assuming your values are the result of
other formulas and you have the display of zeros suppressed.

The INDEX expression you invoked will evaluate empty cells to 0's.

 

[Biff]

The INDEX expression you invoked will evaluate empty cells to 0's.

Yes, if the cells truly are empty.

Looks like an array may be the best solution.

Biff