|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
December 5th, 2008, 03:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Median continued
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 
|
|
#2
December 5th, 2008, 06:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#3
December 5th, 2008, 07:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#4
December 5th, 2008, 07:39 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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!
|
|
#5
December 5th, 2008, 10:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#6
December 8th, 2008, 04:06 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#7
December 8th, 2008, 06:04 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#8
December 9th, 2008, 04:27 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#9
December 9th, 2008, 04:38 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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
|
|
#10
December 9th, 2008, 04:48 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Median continued
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)))
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
