ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Formula help (https://www.excelbanter.com/excel-worksheet-functions/51149-formula-help.html)

Rehanna
Formula help
 
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
Formula help
 
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

Rehanna
Formula help
 
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

Biff
Formula help
 
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

Rehanna
Formula help
 
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

Aladin Akyurek
Formula help
 
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.

Biff
Formula help
 
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

"Rehanna" wrote in message
...
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

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

"Aladin Akyurek" wrote:

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.

Aladin Akyurek
Formula help
 


Biff wrote:
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
Formula help
 
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

"Aladin Akyurek" wrote in message
...


Biff wrote:
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.


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com