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

"Joel" wrote:

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.

"Max" wrote:

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


"Joel" wrote:

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.

"Max" wrote:

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