200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
My data is like this
````
cell A1: 1x
cell A2: 3x
cell A3: 5x
cell A4: empty (empty cell, not text 'empty')
````
In cell A5, I want to output the 40 percentile of values in cells `A1:A4` without the `x` (ignoring empty cells), which will be 2.6 in this case, and add `x` to it. So the desired output of A5 is `2.6x`. In Google Sheets I can use
````
=concatenate(percentile(INDEX(REGEXREPLACE(FILTER(A1:A4,A1:A4<>0)&"","[^\d. ]",)+0),0.4),"x")
````
but not sure how to do it in Excel
Hi and welcome to the Forum
You can use an array formula for this. In Excel365, just enter this in A5:
=PERCENTILE.INC(IFERROR(VALUE(REPLACE(A1:A4,FIND("x",A1:A4),1,"")),""),0.4)=PERCENTILE(IFERROR(VALUE(REPLACE(A1:A4,FIND("x",A1:A4),1,"")),""),0.4)