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
|
|||
|
|||
How to make a conditional Median function?
for my thesis i need to create median industry multipliers. i have a list of
companies with their industry codes and multiples, but now i need to create a list with medians per industry. is there a function similar to SUMIF for medians? |
#2
|
|||
|
|||
How to make a conditional Median function?
|
#3
|
|||
|
|||
How to make a conditional Median function?
i have tried MEDIAN(IF(SHEET1!A2:A100=SHEET2!A2;SHEET1!B2:B100) ) but it returns either a zero or a #value. even though there is a match the formula returns false. entering the matching industry codes in the formula e.g. "100" does not work either. do i need to adjust cell format (now"'general")? thanx. " wrote: Hello, Look he http://www.bettersolutions.com/excel...N616805002.htm HTH, Bernd |
#4
|
|||
|
|||
How to make a conditional Median function?
Hello Myra,
Did you really enter that formula as array formula (not only ENTER but CTRL + SHIFT + ENTER)? Regards, Bernd |
#5
|
|||
|
|||
How to make a conditional Median function?
Thank you. took awhile to get the hang of it. i've been trying the custom function method (since i have to create 300 medians) but why does the MEDIANIF() function differ from the hands on median formula? any ideas how i can use the MEDIANIF function to match on the first 3 digits only and then take a median value? ciao " wrote: Hello Myra, Did you really enter that formula as array formula (not only ENTER but CTRL + SHIFT + ENTER)? Regards, Bernd |
#6
|
|||
|
|||
How to make a conditional Median function?
Hello,
If given the same data these functions should not differ. Could you give a short example how your industry codes and your data look like? Either post it here (anonymous data only) or send me an email. Regards, Bernd |
#7
|
|||
|
|||
How to make a conditional Median function?
example: sheet 1: per firm the industry code in A and data in B (i omitted firmcode) DNUM MULT1A 1000 1,361632912 1000 1,014911371 1000 1,844271002 1000 0,092151887 1000 0,533133521 1000 0,151594133 1000 0,712074691 1000 0,483009013 1040 1080 1080 ........ ........ sheet 2 is for the median value per industry for code=1000 (cell A2) using =MEDIANIF(sheet1!A2:A9;A2;sheet1!B2:B9)= 0,533133507 =MEDIAN(sheet1!B2:B9)= 0,622604106 the median(if()) function gives this answer also. Since not all industry codes have enough data( i need at least 5) i want to take medians of 100* or 10** as well. is this possible with a macro or formula? or shall i continue by hand thnx in advance. myra " wrote: Hello, If given the same data these functions should not differ. Could you give a short example how your industry codes and your data look like? Either post it here (anonymous data only) or send me an email. Regards, Bernd |
#8
|
|||
|
|||
How to make a conditional Median function?
Hello Myra,
The correct code for the UDF is IMHO: Function MEDIANIF(ByVal rgeCriteria As Range, _ ByVal sCriteria As String, _ ByVal rgeMaxRange As Range) As Single Dim iconditioncolno As Integer Dim inumberscolno As Integer Dim lrowno As Long Dim lmatch As Long Dim arsngvalues() As Single Dim sngmedian As Single Dim bsorted As Boolean iconditioncolno = rgeCriteria.Column inumberscolno = rgeMaxRange.Column ReDim arsngvalues(rgeCriteria.Rows.Count) For lrowno = 1 To rgeCriteria.Rows.Count If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, iconditioncolno).Value = sCriteria Then lmatch = lmatch + 1 arsngvalues(lmatch) = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value End If Next lrowno ReDim Preserve arsngvalues(lmatch) Do bsorted = True For lrowno = 2 To lmatch If arsngvalues(lrowno - 1) arsngvalues(lrowno) Then sngmedian = arsngvalues(lrowno - 1) arsngvalues(lrowno - 1) = arsngvalues(lrowno) arsngvalues(lrowno) = sngmedian bsorted = False End If Next lrowno Loop Until bsorted = True If lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) + arsngvalues(1 + lmatch / 2)) / 2 End Function If you enter in sheet2: A1: 1000 B1 (as array formula!): =MEDIAN(IF(Sheet1!$A$3:$A$520=Sheet2!A1,Sheet1!$B$ 3:$B$520,"")) C1: =MEDIAN(Sheet1!B3:B10) D1: =medianif(Sheet1!$A$3:$A$520,Sheet2!A1,Sheet1!$B$3 :$B$520) E1 (as array formula!): =MEDIAN(IF(LEFT(Sheet1!$A$3:$A$520,3)="100",Sheet1 !$B$3:$B$520,"")) Then cells B1:E1 should all show the correct result 0.622604106. E1 gives you an example how to calculate a median of 100*. For 10* you can use LEFT(...,2)="10", for example. Have fun, Bernd |
#9
|
|||
|
|||
How to make a conditional Median function?
hi Bernd,
sorry for being a complete dummy. but now it only spits out #value with a message of a syntax error for: If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) + arsngvalues(1 + lmatch / 2)) / 2 If lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) i made the module exactly as you posted it and at first it seems to be working. so where did I go wrong? the fact that i have a crappy old win98 compu with excel 2000 probably isn't helping either. guess is should have paid more attention during IT at uni. regards. myra " wrote: Hello Myra, The correct code for the UDF is IMHO: Function MEDIANIF(ByVal rgeCriteria As Range, _ ByVal sCriteria As String, _ ByVal rgeMaxRange As Range) As Single Dim iconditioncolno As Integer Dim inumberscolno As Integer Dim lrowno As Long Dim lmatch As Long Dim arsngvalues() As Single Dim sngmedian As Single Dim bsorted As Boolean iconditioncolno = rgeCriteria.Column inumberscolno = rgeMaxRange.Column ReDim arsngvalues(rgeCriteria.Rows.Count) For lrowno = 1 To rgeCriteria.Rows.Count If rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, iconditioncolno).Value = sCriteria Then lmatch = lmatch + 1 arsngvalues(lmatch) = rgeCriteria.Parent.Cells(rgeCriteria.Row + lrowno - 1, inumberscolno).Value End If Next lrowno ReDim Preserve arsngvalues(lmatch) Do bsorted = True For lrowno = 2 To lmatch If arsngvalues(lrowno - 1) arsngvalues(lrowno) Then sngmedian = arsngvalues(lrowno - 1) arsngvalues(lrowno - 1) = arsngvalues(lrowno) arsngvalues(lrowno) = sngmedian bsorted = False End If Next lrowno Loop Until bsorted = True If lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch + 1) / 2) If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2) + arsngvalues(1 + lmatch / 2)) / 2 End Function If you enter in sheet2: A1: 1000 B1 (as array formula!): =MEDIAN(IF(Sheet1!$A$3:$A$520=Sheet2!A1,Sheet1!$B$ 3:$B$520,"")) C1: =MEDIAN(Sheet1!B3:B10) D1: =medianif(Sheet1!$A$3:$A$520,Sheet2!A1,Sheet1!$B$3 :$B$520) E1 (as array formula!): =MEDIAN(IF(LEFT(Sheet1!$A$3:$A$520,3)="100",Sheet1 !$B$3:$B$520,"")) Then cells B1:E1 should all show the correct result 0.622604106. E1 gives you an example how to calculate a median of 100*. For 10* you can use LEFT(...,2)="10", for example. Have fun, Bernd |
#10
|
|||
|
|||
How to make a conditional Median function?
Hello Myra,
If lines are broken after being copied into a module, either combine them again or insert a blank and an underscore (" _") at the end of a broken line. Citation of MS Visual Basic Help: MyVar = "This is an " _ & "example" _ & " of how to continue code." 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 11:54 AM |