Decile calculation Excel
katiaboutamdja
Posted messages
14
Registration date
Status
Membre
Last intervention
-
katiaboutamdja Posted messages 14 Registration date Status Membre Last intervention -
katiaboutamdja Posted messages 14 Registration date Status Membre Last intervention -
Hello,
Hello everyone
I have a table in which I have columns of numbers from row 1 to row 548, and
instead of these numbers, I want to have D1, D2, D3.....up to D9 which correspond to the 9 deciles for each column.
For example, the first number is in row 17 column 8 (because there are empty cells that I want to keep blank): it's 54 (the numbers range from 1 to 100):
I want a formula that tells me which decile this number belongs to, and if it's in decile 3 for example, I want to see D3 and not 17 (D3 is the decile 3 based on the decile calculation of the respective column for each column).
Thank you very much for reading and for any help you can provide.
Configuration: Windows XP / Internet Explorer 7.0
Hello everyone
I have a table in which I have columns of numbers from row 1 to row 548, and
instead of these numbers, I want to have D1, D2, D3.....up to D9 which correspond to the 9 deciles for each column.
For example, the first number is in row 17 column 8 (because there are empty cells that I want to keep blank): it's 54 (the numbers range from 1 to 100):
I want a formula that tells me which decile this number belongs to, and if it's in decile 3 for example, I want to see D3 and not 17 (D3 is the decile 3 based on the decile calculation of the respective column for each column).
Thank you very much for reading and for any help you can provide.
Configuration: Windows XP / Internet Explorer 7.0
6 réponses
Thank you for your response
I have already calculated the deciles for each column
Yeah, it's not easy
I could do this with a VB macro but I can't do it either, I think it's even harder:
I have a code that works well but it's just for one column because of course the decile values change from one column to another:
Here's my macro if you know VBA better, but of course this is only for the deciles of the first column, so I would like to generalize this for all other columns.......
Thank you for your responses.
Sub Decile()
Decile1 = 20.9
Decile2=...........
up to Decile9
Dim w As Worksheet
For Each w In Worksheets
Range("AM20:FB619").Select
For Each Cell In Selection
If Cell.Value < Decile1 Then Cell.Value = D1
.......................
If Cell.Value > Decile9 Then Cell.Value = D10
Next Cell
Next w
End Sub
I have already calculated the deciles for each column
Yeah, it's not easy
I could do this with a VB macro but I can't do it either, I think it's even harder:
I have a code that works well but it's just for one column because of course the decile values change from one column to another:
Here's my macro if you know VBA better, but of course this is only for the deciles of the first column, so I would like to generalize this for all other columns.......
Thank you for your responses.
Sub Decile()
Decile1 = 20.9
Decile2=...........
up to Decile9
Dim w As Worksheet
For Each w In Worksheets
Range("AM20:FB619").Select
For Each Cell In Selection
If Cell.Value < Decile1 Then Cell.Value = D1
.......................
If Cell.Value > Decile9 Then Cell.Value = D10
Next Cell
Next w
End Sub
Hello,
VBA proposal:
select the range and run the macro.
Do some tests to see if it matches what you want
Eric
EDIT: added a small safety feature to avoid manipulation errors.
VBA proposal:
select the range and run the macro.
Sub decile() Dim decile(9) As Double, c As Range, i As Long If MsgBox("Replace with the deciles on " & Selection.Address & ". (" & Selection.Cells.Count & " values)", vbYesNo + vbQuestion, "Confirmation") = vbNo Then Exit Sub For i = 1 To 9 decile(i) = Application.WorksheetFunction.Percentile(Selection, i / 10) Next i For Each c In Selection For i = 9 To 1 Step -1 If c > decile(i) Then Exit For Next i c = "D" & i + 1 Next c End Sub Do some tests to see if it matches what you want
Eric
EDIT: added a small safety feature to avoid manipulation errors.
Thank you very much for your help.
My numbers range from AM20 to FB619 with a lot of empty cells that should remain empty, and with this macro I have D10 popping up everywhere, including where I have empty cells.
Otherwise, when comparing to a column I create in Excel using the percentile function, apart from the D10 I have instead of the empty cells, I have slight differences for the last decile, like I have D10 with this macro and in my column using Excel (the percentile function) I have D9.
And also, in VB, do you know how to use arrays? It's much faster for large datasets; this macro runs in about a minute. But the speed of the macro is secondary; you're already so kind to help me.
Thanks again for your help, it's nice of you.
My numbers range from AM20 to FB619 with a lot of empty cells that should remain empty, and with this macro I have D10 popping up everywhere, including where I have empty cells.
Otherwise, when comparing to a column I create in Excel using the percentile function, apart from the D10 I have instead of the empty cells, I have slight differences for the last decile, like I have D10 with this macro and in my column using Excel (the percentile function) I have D9.
And also, in VB, do you know how to use arrays? It's much faster for large datasets; this macro runs in about a minute. But the speed of the macro is secondary; you're already so kind to help me.
Thanks again for your help, it's nice of you.
Ok, I'll take a look at that tonight (if there are no issues...)
However, it would be interesting if you could upload an anonymized file on cijoint.fr and paste the link here.
Please specify where you find differences (D9-D10). I used If c > decile(i), maybe it should be >= ?
And also specify if you have additional cases other than empty cells to ignore.
Eric
However, it would be interesting if you could upload an anonymized file on cijoint.fr and paste the link here.
Please specify where you find differences (D9-D10). I used If c > decile(i), maybe it should be >= ?
And also specify if you have additional cases other than empty cells to ignore.
Eric
If it doesn't go through cijoint.fr, it will go through email even less...
Lighten it up, the main thing is that there is a range where the difference occurs. And if it's more than 8MB, you can zip it.
Also specify if you only have values in the relevant ranges or if there might be formulas to keep.
eric
PS: Edit your message to remove the email address if you don't want to be spammed even more.
Lighten it up, the main thing is that there is a range where the difference occurs. And if it's more than 8MB, you can zip it.
Also specify if you only have values in the relevant ranges or if there might be formulas to keep.
eric
PS: Edit your message to remove the email address if you don't want to be spammed even more.
I found the error that is likely the cause of the differences (i as long instead of as double)
Check with this version, the example file may not be necessary.
I calculate in memory, it should be faster
eric
Check with this version, the example file may not be necessary.
I calculate in memory, it should be faster
Sub decile() Dim decile(9) As Double, i As Double Dim datas(), lig As Long, col As Long If MsgBox("Replace with deciles on " & Selection.Address & ". (" & Selection.Cells.Count & " values)", vbYesNo + vbQuestion, "Confirmation") = vbNo Then Exit Sub If Selection.Cells.Count = 1 Then Exit Sub datas = Selection For i = 1 To 9 decile(i) = Application.WorksheetFunction.Percentile(datas, i / 10) Next i For lig = 1 To Selection.Rows.Count For col = 1 To Selection.Columns.Count If datas(lig, col) <> "" Then For i = 1 To 9 If datas(lig, col) <= decile(i) Then Exit For Next i datas(lig, col) = "D" & i End If Next col Next lig Selection = datas End Sub eric
Thank you very much for your help
otherwise
for the empty cells it's fine, those cells remain empty
But with this macro it still shows me one or two deciles below or above compared to Excel with the percentile function.
for example, in line 27 with this macro we have D8 D8 D8 D1 D1 D1 D1....
and with my Excel percentile function (in line 1027) D9 D9 D9 D3 D3 D3 D3
So I don't know why
in any case, thank you very much. I'll try to work on it to try to understand why
otherwise
for the empty cells it's fine, those cells remain empty
But with this macro it still shows me one or two deciles below or above compared to Excel with the percentile function.
for example, in line 27 with this macro we have D8 D8 D8 D1 D1 D1 D1....
and with my Excel percentile function (in line 1027) D9 D9 D9 D3 D3 D3 D3
So I don't know why
in any case, thank you very much. I'll try to work on it to try to understand why
I tested it on a small random table; the deciles had the same value in the sheet and in VBA.
Moreover, WorksheetFunction.Percentile calls the percentile() function from the sheets.
Without an example file with the error, it's impossible to look for something...
After you upload your file on cijoint.fr, you need to paste the provided link here.
eric
Moreover, WorksheetFunction.Percentile calls the percentile() function from the sheets.
Without an example file with the error, it's impossible to look for something...
After you upload your file on cijoint.fr, you need to paste the provided link here.
eric
Well..
I don't know why it's different but I'm throwing this out there like that.
In any case, thank you very much.
I don't know why it's different but I'm throwing this out there like that.
In any case, thank you very much.
Hello,
presenting it like this isn't simple.
for your information, you need to use the function percentile(matrix; 0.1): 0.1 represents the decile.
the matrix represents column A or B or C or any other range.
So in summary, we have a matrix (set of cells) that gives a value.
I think we need to recreate another table, but it remains unclear.
See you!
presenting it like this isn't simple.
for your information, you need to use the function percentile(matrix; 0.1): 0.1 represents the decile.
the matrix represents column A or B or C or any other range.
So in summary, we have a matrix (set of cells) that gives a value.
I think we need to recreate another table, but it remains unclear.
See you!