Hi Joel, Thanx for the solution, ill chek it. :-)
"Joel" wrote:
> 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
>
>
> "Max" wrote:
>
> > 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
> > > > > >
> > > > > >
|
Similar Threads
