Calculate Percentile By grouping Data in excel

[Guest]

Hi, i am having DATA like this in Excel which will be growing each month, i
would like to calculate
Percentile 50% of sales by grouping data at Month Level, that means for each
month i ll get differnt percentile value , eg. 2007/01 - Percentile Value,
2007/02 another percentile value.... and so on, is it possible to calculate
this way in need VBA

Customer-Month - Sales
X-2007/01-50000
Y-2007/01-75000
Z-2007/01-80000
X-2007/02-50000
Z-2007/02-80000

Thanks

 

[Guest]

Can you post the worksheet functions you are using. VBA code can do
everything the worksheet function can to and more.....

using the worksheet function
Set CalcRange = Range("B1:B5")
Percentile = worksheetfunction.Percentile(CalcRange,0.5)


From your data it is not clear how to make the Percentile caluclation when
there are more than one data entry for each month.

 

[Guest]

Hi joel,

i want use worksheet function Percentile, but my problem is the range should
be dynamic, and it should automatically group the rows by some value, in my
case grouping based on Month number, and after that it should return 50th
percentile of the sales figures.

Like for below data, if we calculate manually in excel such as

For Month: 2007/01
=Percentile(A1:A3,0.50) will return - 75000

For Month: 2007/02
=Percentile(A4:A5,0.50) will return - 65000


Cell_Ref-Customer-Month-Sales

A1:X-2007/01-50000
A2:Y-2007/01-75000
A3:Z-2007/01-80000
A4:X-2007/02-50000
A5:Z-2007/02-80000



Thanx

 

[Guest]

Couple of quick questions. The sales quantity need to be in a sperate column
for the Percentile function to work. Should I assume the data is in 3
columns. Column A is x,y,z. Column B is date. Column C is sales quantity.

Also where do you want to put the results? Is the Last Row of the month ok.
I would write a macro that would add the formula into the worksheet, not just
the results. This way if any number is changed the results will
automatically change.

 

[Guest]

Hi joel,

u r right, sales quantity in seperate column, Yes data is in 3 column. Yes
it is perfectly fine if Result is put at the end of row

Thanx for quick reply

 

[Guest]

The code check dates in column B. If the date in a row does not match the
date in the nex row the code adds the Percentile formula in column d. The
percentile formuila uses the Sales figures in Column C. FirstRow is changed
everytime a new date is found so a Range (row number) of cells can be
inserted into the Percentile formula. CalcRange is a string that represents
the range of cells C1:C3. PercentFormula is another string which is simply
the formula that gets added into the worksheet =PERCENTILE(C1:C3,0.5)

Actually looking at the code is easier to understand than my written
explanation.

Sub calc_percentile()

FirstRow = 1
RowCount = 1
Do While Cells(RowCount, "A") <> ""
If Cells(RowCount, "B") <> _
Cells(RowCount + 1, "B") Then

CalcRange = _
"C" & FirstRow & ":C" & RowCount

PercentFormula = _
"=Percentile(" & CalcRange & ",0.5)"
Cells(RowCount, "D").Formula = PercentFormula
FirstRow = RowCount + 1
End If
RowCount = RowCount + 1
Loop
End Sub

 

[Guest]

Hi Joel, Thanx for the solution, ill chek it.