0

The code currently asks the user for two separate numbers, and it divides them and the answer pops up in a message box. What I want to do next is use that answer and figure what percentile it is within column "T" in my workbook. 

 If response = vbNo Then
    Dim cost, weight, answer As Variant
        cost = InputBox("Please Enter PO Cost")
        weight = InputBox("Please Enter Net Weight")
        answer = cost / weight
        MsgBox "Price per KG is: " & answer
        Exit Sub
cam
  • 57
  • 1
  • 11
  • Hello cam, just to clarify, if you are considering the lowest 10%, are you expecting 0.1 or 1 if the list of values is (1,2,3,...,10)? – Yarnspinner Jun 15 '16 at 13:14
  • @Yarnspinner I would be expecting 0.1...What the end goal of this code is to take that answer from the division problem, and check to verify that the answer is within a certain range of all of the values on column "T". Thus, my solution is to display what percentile that answer falls in within the column "T", to tell the user if their answer is close to the average of that column. – cam Jun 15 '16 at 13:19
  • WorksheetFunction.Percentrank is what you are looking for then. It takes in an array and a value then returns you the 0.1% in this case. Percentile is the inverse, u pass in an array and 0.1 to get back 1. – Yarnspinner Jun 15 '16 at 13:27

2 Answers2

1

You can use the percentile worksheet function as follows.

Public Sub Percentile()
    Dim myrng As Range

    Set myrng = Range("t1:t10") 'set the range
    mypercentile = WorksheetFunction.Percentile(myrng, 0.9) ' Retrieve the 90th percentile from t1:t10
    MsgBox ("The percentile is " & mypercentile)
End Sub
Ryan Wildry
  • 5,612
  • 1
  • 15
  • 35
1

You can make use of Excel's inbuilt Percentrank function. I'm assuming there aren't any blanks in column T.

Dim x as double
Set ws= ActiveWorkbook.Worksheets("Sheet1")
Set relevant_array = ws.Range(ws.Range("T1"),ws.Range("T1").End(xlDown))
x = WorksheetFunction.Percentrank(relevant_array.Address,answer)
debug.print x
Yarnspinner
  • 852
  • 5
  • 7
  • I am trying to rework this code to work for me, and I keep getting an error on the last line, says that it requires an "=", or when I set it to something, it says "Assignment to constant not permitted" – cam Jun 15 '16 at 14:15
  • Sorry, this should be my fault. This is a worksheet function so it doesn't take in a VBA range and expects a string instead. Should work if you change it to "T1:T500" or relevant_array.Address. Sorry, dont have Excel with me now so couldnt check. Wait no, that isn't the case after further reading. I'm not too sure what is causing the error. – Yarnspinner Jun 15 '16 at 14:26
  • @cam, ok the new code should work fine. The actual error was that percentrank returned a value which needed to be assigned to a variable. I declared x as a variable in the code and now it runs fine. – Yarnspinner Jun 15 '16 at 14:33
  • thanks for your work on this. The code looks great and it truly should work, now I'm getting the error "Unable to get the PercentRank property of the WorksheetFunction class. I believe this is a problem with my excel setup, not your code. – cam Jun 15 '16 at 14:40
  • @cam, it could be that the answer you pass in isnt being treated as a numeric value. Apparently, a common cause of that error according to http://stackoverflow.com/questions/10635048/excel-error-1004-unable-to-get-property-of-worksheetfunction-class-appear is passing in unexpected data types into a function. You could try debug.print answer and TypeName(answer) to check that out. – Yarnspinner Jun 15 '16 at 14:49