A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to make a conditional Median function?



 
 
Thread Tools Display Modes
  #11  
Old May 12th, 2006, 10:13 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old May 12th, 2006, 11:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default 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  
Old December 9th, 2006, 03:38 PM posted to microsoft.public.excel.worksheet.functions
Ed Ford
external usenet poster
 
Posts: 2
Default 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  
Old December 9th, 2006, 07:57 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old December 10th, 2006, 09:44 PM posted to microsoft.public.excel.worksheet.functions
Ed Ford
external usenet poster
 
Posts: 2
Default 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  
Old December 11th, 2006, 03:45 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old June 4th, 2010, 03:28 PM posted to microsoft.public.excel.worksheet.functions
rwtrader
external usenet poster
 
Posts: 8
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 11:54 AM


All times are GMT +1. The time now is 01:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.