Quartile and Deciles in Excel

[thisisrahul]

Hi,
I want a function in Excel that gives me quartile position (e.g. Q1, Q2, Q3 and Q4) or Decile position (e.g. D1, D2 ets) of a number in the data set.

for example in the given data set
1,2,3,4,2,7

1 belongs to Q1
7 belongs to Q4

similar for Decile also.

I also want a way where i can define if Higher is better or Lower is better.


any VBA help is welcome.


__________
Rahul

 

[mole999]

Was the response provide here for you > https://www.mrexcel.com/forum/excel-questions/989215-quartile-deciles-excel.html#post4746100 not suitable ?

 

[thisisrahul]

thanks mole999, this is very close to what i want.
i can derive my result from the help that you provided.

 

[thisisrahul]

mole999,
is there a way by which i can reverse the sequence of quartile.
say lowest value belong to Q4.

 

[mole999]

Pure speculation 1,2,3,4 to 4,3,2,1 maybe (I don't actually know that's what it will show)