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 |
#11
|
|||
|
|||
How to make a conditional Median function?
Using the Array Formula does work but you must use a comma separator between
your function arguments and not a semi-colon Please use the following formula MEDIAN(IF(SHEET1!A2:A100=SHEET2!A2,SHEET1!B2:B100) ) and enter it using (Ctrl + Shift + Enter). regards BetterSolutions.com |
#12
|
|||
|
|||
How to make a conditional Median function?
Many thanks to Bernd for finding a mistake in the VBA MEDIANIF function.
The function was returning an incorrect value when the total number of matches was an even number: The incorrect line was: If lmatch Mod 2 = 0 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) The correct line should read: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +arsngvalues(1 + (lmatch / 2))) / 2 This has been corrected on the website. |
#13
|
|||
|
|||
How to make a conditional Median function?
Dear BetterSolutions:
I successfully used the array formula for a conditional median. I would like to create a conditional median formula with more than one condition (about 4 "and" conditions). I've tried what seemed reasonable but no luck. I'd appreciate any thoughts. -- Ed Ford "BetterSolutions.com" wrote: Many thanks to Bernd for finding a mistake in the VBA MEDIANIF function. The function was returning an incorrect value when the total number of matches was an even number: The incorrect line was: If lmatch Mod 2 = 0 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) The correct line should read: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +arsngvalues(1 + (lmatch / 2))) / 2 This has been corrected on the website. |
#14
|
|||
|
|||
How to make a conditional Median function?
Try something like this (array entered):
=MEDIAN(IF((A1:A10="a")*(B1:B10="b")*(C1:C10="c")* (D110="d"),E1:E10)) Biff "Ed Ford" wrote in message ... Dear BetterSolutions: I successfully used the array formula for a conditional median. I would like to create a conditional median formula with more than one condition (about 4 "and" conditions). I've tried what seemed reasonable but no luck. I'd appreciate any thoughts. -- Ed Ford "BetterSolutions.com" wrote: Many thanks to Bernd for finding a mistake in the VBA MEDIANIF function. The function was returning an incorrect value when the total number of matches was an even number: The incorrect line was: If lmatch Mod 2 = 0 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) The correct line should read: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +arsngvalues(1 + (lmatch / 2))) / 2 This has been corrected on the website. |
#15
|
|||
|
|||
How to make a conditional Median function?
Thank you very much. It worked fine. I had gotten obsessed with using the
AND function and missed this tidy approach. -- Ed Ford "T. Valko" wrote: Try something like this (array entered): =MEDIAN(IF((A1:A10="a")*(B1:B10="b")*(C1:C10="c")* (D110="d"),E1:E10)) Biff "Ed Ford" wrote in message ... Dear BetterSolutions: I successfully used the array formula for a conditional median. I would like to create a conditional median formula with more than one condition (about 4 "and" conditions). I've tried what seemed reasonable but no luck. I'd appreciate any thoughts. -- Ed Ford "BetterSolutions.com" wrote: Many thanks to Bernd for finding a mistake in the VBA MEDIANIF function. The function was returning an incorrect value when the total number of matches was an even number: The incorrect line was: If lmatch Mod 2 = 0 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) The correct line should read: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +arsngvalues(1 + (lmatch / 2))) / 2 This has been corrected on the website. |
#16
|
|||
|
|||
How to make a conditional Median function?
You're welcome. Thanks for the feedback!
Biff "Ed Ford" wrote in message ... Thank you very much. It worked fine. I had gotten obsessed with using the AND function and missed this tidy approach. -- Ed Ford "T. Valko" wrote: Try something like this (array entered): =MEDIAN(IF((A1:A10="a")*(B1:B10="b")*(C1:C10="c")* (D110="d"),E1:E10)) Biff "Ed Ford" wrote in message ... Dear BetterSolutions: I successfully used the array formula for a conditional median. I would like to create a conditional median formula with more than one condition (about 4 "and" conditions). I've tried what seemed reasonable but no luck. I'd appreciate any thoughts. -- Ed Ford "BetterSolutions.com" wrote: Many thanks to Bernd for finding a mistake in the VBA MEDIANIF function. The function was returning an incorrect value when the total number of matches was an even number: The incorrect line was: If lmatch Mod 2 = 0 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) The correct line should read: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) +arsngvalues(1 + (lmatch / 2))) / 2 This has been corrected on the website. |
#17
|
|||
|
|||
How to make a conditional Median function?
Looks like this link is no longer valid...
" wrote: Hello, Look he http://www.bettersolutions.com/excel...N616805002.htm HTH, Bernd |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
make it possible to get to the function reference for excel when | an unhappy excel user | General Discussion | 1 | April 21st, 2006 12:12 AM |
Conditional average function | Andres | Worksheet Functions | 1 | August 9th, 2005 06:31 PM |
Automatically up date time in a cell | Mark | General Discussion | 5 | May 12th, 2005 12:26 AM |
clock | Wildman | Worksheet Functions | 2 | April 26th, 2005 10:31 AM |
Freezing Conditional Formatting | Ashish Chamaria | Worksheet Functions | 3 | December 19th, 2003 10:54 AM |