Hi everyone, I am working on a UDF to calculate quartiles (the ones provided in Excel do not calculate the way I want). However, when I call on the function and type in the parameters, I get a VALUE error. Can someone help me with where I'm going wrong?
(btw, this is my first UDF, so I apologize if something is fundamentally wrong with it)
Thanks!
Please Login or Register to view this content.
Last edited by ATLGator; 06-18-2014 at 09:50 AM.
The code itself looks basically ok at first glance, although it needs an error handler (e.g. there are no numbers in the range, or there are error values). Can you post a workbook showing it failing?
Everyone who confuses correlation and causation ends up dead.
The formula is not producing an error anymore, but its not giving me the results I expected. Here's a sample file.
Part of the problem is that your Q0, Q1 etc variables should be Double, not Long.
Also, in your "rng Mod 4 = 3" section, I think you should have:
Please Login or Register to view this content.
Last edited by romperstomper; 06-18-2014 at 10:18 AM.
WorksheetFunction.mod is not available to VBA - use VBA's MOD instead, like so:
Please Login or Register to view this content.
Bernie Deitrick
Excel MVP 2000-2010
BTW, Percentile returns the same results, for this range at least.
for this range, but when you start changing the amount of numbers in the range, the expected values don't come up.
Try:
73
77
79
80
84
85
86
86
87
91
92
94
With my formula, Q1 should be 79.5 and Q3 should be 89. Percentile gives you 79.75 and 88, respectively.
romperstomper - I updated all to Double, and I actually caught the SMALL after posting, but did change it
Bernie - Worksheetfunction.mod was in the original posting, but I quickly updated after see MOD in another post (obviously, not quickly enough). I did make that change though.
Anything else you see?
Updated code:
Please Login or Register to view this content.
Success, thanks for all your help:
Please Login or Register to view this content.
Try it this way -
Please Login or Register to view this content.
Last edited by Bernie Deitrick; 06-18-2014 at 11:45 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks