Nov 23 2021 08:30 PM
Hi,
I have a large data set, which contains many entities. I am looking to find the value of a percentile, using the percentile function on excel. To have the formula only select the entity and check in question i am looking to use the IF function. However i seem to not be able to get it to work.
My two criteria are to match the "entity code" & "validation" in columns A & B. Any help or insight on what i am doing incorrectly would be very much appreciated.
Kind regards,
Nov 23 2021 11:31 PM
Solution@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.
Nov 24 2021 03:29 PM - edited Nov 24 2021 03:30 PM
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,
Nov 24 2021 08:46 PM
@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.
Nov 24 2021 09:19 PM
Nov 24 2021 10:53 PM
@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.
Nov 23 2021 11:31 PM
Solution@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.