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 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.
Hi Riny,
Thank you kindly for your assistance. From your comments i noticed i needed to copy down my formula in column E, so now it has the full set. I did notice that when i did this, i no longer have the results for row 335 and 336. To run the formula, i just pressed calculate now. Is there an additional step i need to do?
Many thanks for your assistance.
Kind regards,
calof1 You're welcome! With regard to sheet calculation, you can set it back to Auto. I just switched it off s it became to slow on my machine, at least.
- Thanks i have turned back on. I have noticed all the calculations show "-". However the formula works for some rows when the data in column E was less. Does this mean the size of the data is too big to calculate?
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.