Forum Discussion
Help using Nested IF formula in PERCENTILE
calof1 I altered the formula quite a bit and it has become a monster. The sheet calculates very slow (at least, on my machine) so I set it to calculate manually. Then, there isn't much data in column E. Only the first 193 rows out of 86000 contain a number. Only two rows in the Proposed Tolerance sheet (rows 335 and 336) return a numeric result. I trust that your real data set has more numbers in it.
calof1 The hyphen is an error trap. If the formula somehow produces an error it returns the hyphen, rather than displaying #NUM, #CALC, "NA# or something similar. But it should not return an error actually. Played around a bit with the ranges the formula references and there may indeed be a limitation to the size of the array PERCENTILE can handle. Couldn't find anything about it on line, but on my system the formula works fine with arrays up to 20840 elements. Thus not the classical 16384 or 32768 that are limits you often find. So, referencing A2:A20841 goes fine. Extend it by one more row and you'll get the hyphen. At least that's on my system. Perhaps it's a memory limitation. Don't know.