OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from the
actual value and square the result to remove any negatives. Then the min of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to determine the
Median

No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and use the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle values (if there are an even
number of values) or the middle number (if there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from the
actual value and square the result to remove any negatives. Then the min of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to determine the
Median

Bernie,
You're my hero! Anytime you are near Aldershot, Hampshire - ping me a mail
- I owe you a beer for that! Great solution thanks!

Bony

"Bernie Deitrick" wrote:

No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and use the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle values (if there are an even
number of values) or the middle number (if there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from the
actual value and square the result to remove any negatives. Then the min of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to determine the
Median

Can't you just email me one? ;-)

Thanks for letting me know that my solution worked for you.

Bernie
MS Excel MVP


Anytime you are near Aldershot, Hampshire - ping me a mail
- I owe you a beer for that! Great solution thanks!

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to determine
the
Median

Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to determine
the
Median

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median

Hi Biff,
Not trying to sound ungrateful but ...

your suggested solution returns a TRUE for every cell in range a4:f4. I
tried variations of it but still couldn't get it working properly. Sorry. :)

Best regards,
Bony

"T. Valko" wrote:

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median

Ah! it works as an array formula!

However, I don't believe ctrl sh enter works in conditional formula ...

"T. Valko" wrote:

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median

It should work in the CF without entry with Cntrl-Shift-Enter

HTH,
Bernie
MS Excel MVP


Ah! it works as an array formula!

However, I don't believe ctrl sh enter works in conditional formula ...

"T. Valko" wrote:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

Formulas used in conditional formatting and in data validation are
automatically processed as arrays so you don't have to array enter them in
the refedits (those little boxes in dialogue forms).

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Ah! it works as an array formula!

However, I don't believe ctrl sh enter works in conditional formula ...

"T. Valko" wrote:

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell,
and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two
middle
values (if there are an even number of values) or the middle number
(if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base
Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted
to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median
from
the
actual value and square the result to remove any negatives. Then
the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4
to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median

Thanks again guys! I can honestly say that I didn't know about automatic
array entry in CF. I just assumed that as it wouldn't accept the Ctrl Sh
Enter ... well you know ...

Isn't learning fun!

Best regards,
Bony

"Bony Pony" wrote:

Ah! it works as an array formula!

However, I don't believe ctrl sh enter works in conditional formula ...

"T. Valko" wrote:

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell, and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two middle
values (if there are an even number of values) or the middle number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median from
the
actual value and square the result to remove any negatives. Then the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median

Isn't learning fun!

You betcha! :)

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Thanks again guys! I can honestly say that I didn't know about automatic
array entry in CF. I just assumed that as it wouldn't accept the Ctrl Sh
Enter ... well you know ...

Isn't learning fun!

Best regards,
Bony

"Bony Pony" wrote:

Ah! it works as an array formula!

However, I don't believe ctrl sh enter works in conditional formula ...

"T. Valko" wrote:

Cheers!

--
Biff
Microsoft Excel MVP


"Bony Pony" wrote in message
...
Aargh!! There are SO many ways to do something in Excel!

LOL - Many many thanks and a beer for each of you is in the mail :)

"T. Valko" wrote:

A few keystrokes shorter:

=ABS(MEDIAN($A4:$F4)-A4)=MIN(ABS($A4:$F4-MEDIAN($A4:$F4)))

--
Biff
Microsoft Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
No need to use a cell to do the calculation.

Use the "Formula is" option of the CF, with A4 as the active cell,
and
use
the formula

=OR(A4=LARGE($A$4:$F$4,COUNT($A$4:$F$4)/2),IF(MOD(COUNT($A$4:$F$4),2)=0,A4=LARGE($A$4:$F$4 ,COUNT($A$4:$F$4)/2+1),FALSE))

This works because the median is either the average of the two
middle
values (if there are an even number of values) or the middle
number (if
there are an odd number of values).

HTH,
Bernie
MS Excel MVP


"Bony Pony" wrote in message
...
OK then. I'm going in circles on this.

I want to conditionally format the cell that contains the Median
or
closest
thereto.

I have a row of cells in different currencies eg

a b c d e f
1 USD GBP EUR CHF ZAR AUD
2 10000 13500 17000 8500 12500 4500 Base
Currency
3 1 1.54 1.27 .84 .1 .66
Conversion rate to USD
4 10000 20847 21613 7175 1232 2956 Converted
to
USD

=median(a4:f4) results in 8587 which does not appear in row 4

In another method which uses multiple rows, I subtract the median
from
the
actual value and square the result to remove any negatives. Then
the
min
of
that is closest to the median and therefore the answer.

But I want to do this in one cell as a conditional format behind
A4 to
F4.

Any help deeply appreciated!!

Best regards,
Bony




I want to normalise these to a single currency (USD) so as to
determine
the
Median