Copy the text of the VBA code in my previous posting, and paste into the VBA editing pane on the right.ĥ. In the VBA window, click Insert, then Module to create a normal module.ģ. Right-click the worksheeet tab labeled Tabellenblatt1, and click View Code. To create the VBA function weightedMedian.ġ. As demonstrated, it might differ significantly from theĪctual median (10) calculated by the VBA function weightedMedian. But note that the weighted median calculated in this manner (9.025) is only anĮstimate. Used in this manner, FORECAST interpolates between the days in A11:A12 by prorating the midpoint between the cumulative counts in C11:C12. D2), and replace MATCH(C53/2,C2:C53)-1 with D2 in the formula above. Of course, it would be more efficient to enter =MATCH(C53/2,C2:C53)-1 into another helper cell (e.g. Then the (estimated) weighted median is (9.025): Construct a helper column with the cumulative counts. This is due to the method of estimating the weighted median that I alluded to before. So i tried to calculate the median by hand and got 9,03 as a result. I've included instructions below, in case you are still interested. Perhaps it is just a matter of knowing how. I don't know why you would be prevented ("cannot") from creating and executing the VBA code. The VBA code is really helpful, but i cannot enter it my excel wont execute The VBA function returns an actual median.Īlternatively, there is a method for estimating the weighted median of grouped data. WeightedMedian = WorksheetFunction.Median(vdata) Where weightedMedian is the following VBA code in a normal module (not a worksheet object):įunction weightedMedian(v As Range, cnt As Range)ĭim n As Long, k As Long, i As Long, j As Longįor i = 1 To v.Rows.Count: For j = 1 To cnt(i) Translate English function names appropriately.
Read period as the decimal point (comma, for you). Standard deviation 6,670011739 variance 44,4890566Ĭaveat: Change parameter separators from comma to semicolon.
0 kommentar(er)
