Extracting Data from 2 cells into 1

[Guest]

Trying to find median for a list of grades.
Each Column has a grade and below each row has the # of exams the student
has received that grade.

4.0 3.5 3.0 2.5
Student 1 2 1 1 2

How can I have Excel pull out: 4.0, 4.0, 3.5, 3.5, 2.5, 2.5 from the table
above? We are trying to avoid having to type it out...we want to simply put
the # of times that grade was received and then have Excel list the data
individually in cells so we can generate a median value. Thank you!

 

[PCLIVE]

Based on the data you've provided, you might try:

=LEFT(REPT(A1&", ",A2)&REPT(B1&", ",B2)&REPT(C1&", ",C2)&REPT(D1&",
",D2),LEN(REPT(A1&", ",A2)&REPT(B1&", ",B2)&REPT(C1&", ",C2)&REPT(D1&",
",D2))-2)

However, if you have more columns, then you'll need to adjust the formula.

HTH,
Paul

 

[Dave Peterson]

I'd use a UDF (a macro???).

Is that ok?

If yes:

Option Explicit
Function WeightedMedian(ScoreRng As Range, CtrRng As Range) As Variant

Dim ScoreArr() As Double
Dim myCell As Range
Dim rCtr As Long
Dim iCtr As Long
Dim WhichScore As Long

'some minor checking
If ScoreRng.Areas.Count _
And CtrRng.Areas.Count = 1 Then
'ok
Else
WeightedMedian = "One area each!"
Exit Function
End If

If (ScoreRng.Columns.Count = 1 _
Or ScoreRng.Rows.Count = 1) _
And (CtrRng.Columns.Count = 1 _
Or CtrRng.Rows.Count = 1) Then
'ok
Else
WeightedMedian = "Each Range must have one row or one column"
Exit Function
End If

If ScoreRng.Cells.Count = CtrRng.Cells.Count Then
'ok
Else
WeightedMedian = "Mismatched cell count"
Exit Function
End If

ReDim ScoreArr(1 To Application.Sum(CtrRng))

WhichScore = 0
For Each myCell In CtrRng.Cells
WhichScore = WhichScore + 1
For rCtr = 1 To myCell.Value
iCtr = iCtr + 1
ScoreArr(iCtr) = Application.Index(ScoreRng, WhichScore)
Next rCtr
Next myCell

WeightedMedian = Application.Median(ScoreArr)
End Function


You'd use it like this:
=weightedmedian($B$1:$E$1,B2:E2)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=weightedmedian($B$1:$E$1,B2:E2)

 

[Guest]

If all you really need is just the median and you dont have too many high #s
in the
try for example for a 7 max #
=MEDIAN($A$1:$D$1,IF($A$2:$D$2>1,$A$1:$D$1),IF($A$2:$D$2>2,$A$1:$D$1),IF($A$2:$D$2>3,$A$1:$D$1),IF($A$2:$D$2>4,$A$1:$D$1),IF($A$2:$D$2>5,$A$1:$D$1),IF($A$2:$D$2>,$A$1:$D$1))
entered as an array (contol-shift-enter)
another short hand method is to sort the data by the scores
add a helper column with cumilative total for # and look for # equal to or
just above total/ 2