Excel PERCENTILE function without 0

[Wyvern]

I found an appropriate function in Excel called ''PERCENTILE'' almost to my needs! The item which does not suit me is that the function takes into account the value 0 and I do not want to take into account this value. Is there anyone who can provide me a trick to use Excel PERCENTILE function without 0? If its not possible then is there any other function suitable for the same?

[CitricAcid]

Ok, I have an idea. In another cell C1 you put the value which is 0 or not (or reference) and in your percentile calculation cell, you add the formula in a condition as follows:

 = IF (C1 = 0; "error"; PERCENTILE (...))

[Wyvern]

Sorry but the proposal: = IF (C1 = 0; "error"; PERCENTILE (...)) can not function because that percentile function takes into account a range of data and values can be 0 in several cells.

I think that function has not been well understood. Thank you anyway.

[!const]

Try:

= IF (PERCENTILE (A2: B4; 0) = 0; "error"; PERCENTILE (A2: B4; 0))

[DotNetUser]

The solution will involve a matrix

= PERCENTILE (IF (range = 0; ""; range ), k)

to validate array (Ctrl + Shift + Enter)

For example, to find the 90th percentile in a range of value from A1 to A500 omitting the 0, you can use the array formula

= PERCENTILE (IF (A1: A500 = 0; "", A1: A500); 0.9)

to validate array (Ctrl + Shift + Enter)

[Wyvern]

Thank you for your answers and your help. I tried the formula

= PERCENTILE (IF (range = 0; ""; range ), k)

without the braces on both sides of the formula. The result is correct only if I look at it by opening the window by clicking on FX (in the toolbar) if the value in the cell is wrong because it takes account of 0.

I'd like to try with the braces but I can not confirm the function. (Ctrl + Shift + Enter) does not work!

= PERCENTILE (IF (A2: A500 = 0; "", A2: A500); 0.9)

The message indicates that the formula contains an error.

[DotNetUser]

For validation matrix, do not put braces. It should normally take the form and confirm with Ctrl + Shift + Enter. Excel adds the braces during validation.

So, remove the brackets in your formula, enter the formula

 = PERCENTILE (IF (A2: A500 = 0; "", A2: A500), 0.9)

Validate matrix, and then you shall

 (= PERCENTILE (IF (A2: A500 = 0; "", A2: A500), 0.9))

in the formula bar