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