software

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?

Hello,

Look he
www.bettersolutions.com/excel...N616805002.htm

HTH,
Bernd
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. quot;100quot; does not work
either.
do i need to adjust cell format (nowquot;'generalquot;)?

thanx.

quot; wrote:

gt; Hello,
gt;
gt; Look he
gt; www.bettersolutions.com/excel...N616805002.htm
gt;
gt; HTH,
gt; Bernd
gt;
gt;

Hello Myra,

Did you really enter that formula as array formula (not only ENTER but
CTRL SHIFT ENTER)?

Regards,
Bernd
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
quot; wrote:

gt; Hello Myra,
gt;
gt; Did you really enter that formula as array formula (not only ENTER but
gt; CTRL SHIFT ENTER)?
gt;
gt; Regards,
gt; Bernd
gt;
gt;

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
example:
sheet 1: per firm the industry code in A and data in B (i omitted firmcode)
DNUMMULT1A
10001,361632912
10001,014911371
10001,844271002
10000,092151887
10000,533133521
10000,151594133
10000,712074691
10000,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

quot; wrote:

gt; Hello,
gt;
gt; If given the same data these functions should not differ.
gt;
gt; Could you give a short example how your industry codes and your data
gt; look like?
gt;
gt; Either post it here (anonymous data only) or send me an email.
gt;
gt; Regards,
gt; Bernd
gt;
gt;

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) gt; 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,quot;quot;))
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)=quot;100quot;,Sheet1 !$B$3:$B$520,quot;quot;))

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)=quot;10quot;, for example.

Have fun,
Berndhi 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
quot; wrote:

gt; Hello Myra,
gt;
gt; The correct code for the UDF is IMHO:
gt; Function MEDIANIF(ByVal rgeCriteria As Range, _
gt; ByVal sCriteria As String, _
gt; ByVal rgeMaxRange As Range) As Single
gt;
gt; Dim iconditioncolno As Integer
gt; Dim inumberscolno As Integer
gt; Dim lrowno As Long
gt; Dim lmatch As Long
gt; Dim arsngvalues() As Single
gt; Dim sngmedian As Single
gt; Dim bsorted As Boolean
gt;
gt; iconditioncolno = rgeCriteria.Column
gt; inumberscolno = rgeMaxRange.Column
gt; ReDim arsngvalues(rgeCriteria.Rows.Count)
gt;
gt; For lrowno = 1 To rgeCriteria.Rows.Count
gt; If rgeCriteria.Parent.Cells(rgeCriteria.Row lrowno - 1,
gt; iconditioncolno).Value = sCriteria Then
gt; lmatch = lmatch 1
gt; arsngvalues(lmatch) = rgeCriteria.Parent.Cells(rgeCriteria.Row
gt; lrowno - 1, inumberscolno).Value
gt; End If
gt; Next lrowno
gt; ReDim Preserve arsngvalues(lmatch)
gt; Do
gt; bsorted = True
gt; For lrowno = 2 To lmatch
gt; If arsngvalues(lrowno - 1) gt; arsngvalues(lrowno) Then
gt; sngmedian = arsngvalues(lrowno - 1)
gt; arsngvalues(lrowno - 1) = arsngvalues(lrowno)
gt; arsngvalues(lrowno) = sngmedian
gt; bsorted = False
gt; End If
gt; Next lrowno
gt; Loop Until bsorted = True
gt;
gt; If lmatch Mod 2 = 1 Then MEDIANIF = arsngvalues((lmatch 1) / 2)
gt; If lmatch Mod 2 = 0 Then MEDIANIF = (arsngvalues(lmatch / 2)
gt; arsngvalues(1 lmatch / 2)) / 2
gt; End Function
gt;
gt; If you enter in sheet2:
gt; A1:
gt; 1000
gt; B1 (as array formula!):
gt; =MEDIAN(IF(Sheet1!$A$3:$A$520=Sheet2!A1,Sheet1!$B$ 3:$B$520,quot;quot;))
gt; C1:
gt; =MEDIAN(Sheet1!B3:B10)
gt; D1:
gt; =medianif(Sheet1!$A$3:$A$520,Sheet2!A1,Sheet1!$B$3 :$B$520)
gt; E1 (as array formula!):
gt; =MEDIAN(IF(LEFT(Sheet1!$A$3:$A$520,3)=quot;100quot;,Sheet1 !$B$3:$B$520,quot;quot;))
gt;
gt; Then cells B1:E1 should all show the correct result 0.622604106. E1
gt; gives you an example how to calculate a median of 100*. For 10* you can
gt; use LEFT(...,2)=quot;10quot;, for example.
gt;
gt; Have fun,
gt; Bernd
gt;
gt;

Hello Myra,

If lines are broken after being copied into a module, either combine
them again or insert a blank and an underscore (quot; _quot;) at the end of a
broken line.

Citation of MS Visual Basic Help:
MyVar = quot;This is an quot; _
amp; quot;examplequot; _
amp; quot; of how to continue code.quot;

HTH,
Bernd