Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Percentile By grouping Data in excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
percentile - data in 2 different ranges | Excel Discussion (Misc queries) | |||
Use excel to calculate a percentile from data in multiple sheets | Excel Worksheet Functions | |||
calculate percentile for indexed numbers. Thanks | New Users to Excel | |||
calculate percentile for indexed numbers. Thanks | New Users to Excel | |||
Percentile - Data in arrays | Excel Programming |