Median continued

[Bony Pony]

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 Deitrick]

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]

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]

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

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

Bernie
MS Excel MVP

 

[T. Valko]

A few keystrokes shorter:

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

 

[Bony Pony]

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]

Cheers!

--
Biff
Microsoft Excel MVP

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

 

[Bony Pony]

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

 

[Bony Pony]

Ah! it works as an array formula!

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

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

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).

 

[Bony Pony]

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

 

[T. Valko]

Isn't learning fun!

You betcha!