View Poll Results: Will my post be reolved

Voters
1. This poll is closed
  • Yes

    1 100.00%
  • No

    0 0%
+ Reply to Thread
Results 1 to 39 of 39

Quintile Formula - Function Wise - Help need for formula calculation

  1. #1
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Quintile Formula - Function Wise - Help need for formula calculation

    Hi All,

    Need help in creating a formula which helps me in calculating Percentrank (Quintile) based on the Function the employees working with.

    Attached document, I have calculated Percentrank (Quintile) based on overall employees scores (Highlighted in Yellow), however, I need a formula where Percentrank (Quintile) is calculated based on Function (Highlighted in Blue & Orange) employees working with and that too formula needs to be created in such a way that Percentrank (Quintile) is calculated within the same function and once function changes, Percentrank (Quintile) should change and calculate accordingly to changed function.

    I would be pleased, if any one could assist and help me in creating this formula.

    Any doubts, please do revert on this thread would be much appreciated.Working file.xlsb

    Thank you, awaiting for your kind response and assistance on this regards.

    Regards,
    Yadavagiri
    Last edited by alansidman; 12-16-2015 at 06:12 PM. Reason: CHANGE OF TITLE

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Quintile Formula - Function Wise

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise

    Thanks Proton for the feedback, Title amended accordingly, Hope this is fine. Sorry for any inconvenience.

    Regards,
    Yadavagiri

  4. #4
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi,

    Any help on this please...

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    I looked at this yesterday.... and got confused. Today, I'll have a go. I don't know what the expected answers are, but...try this array formula:
    =MATCH(C2,PERCENTILE(IF(B2=$B$2:$B$33,$C$2:$C$33),{5,4,3,2,1}/5),-1)

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Incidentally, your overall percentile formula can also be shortened to:
    =MATCH(C2,PERCENTILE($C$2:$C$33,{5,4,3,2,1}/5),-1)
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Thanks Glenn, This was very useful. Itreally helped as expected. Much appreciated!!

    I checked with this formula now, it really works for the scores in the pattern higher to lower. However, when I try implementing the percentile where lower score should get better percentile and higher percentile to get lower percentile, it doesn't work.

    I have attached the document and have highlighted the column in orange/red.

    Would be grateful, if you could help on this please.

    Regards,
    Yadavagiri

  8. #8
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Working file.xlsb

    attachment

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    I'm away for an hour or so. Do note that you HAD NOT entered the queue percentile as an array formula. Re-read my last post.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Isn't the lower to higher order simply given by

    =6-D2

    copied down???

  11. #11
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Thanks Glenn, I am sorry for that I didn't include array formula as you advised earlier. However, I have now updated in my file accordingly. Thanks for rectifying my mistake.

    I will await for your response once you are back on the percentile calculation for lower to higher scores.

    Regards,
    Yadavagiri

  12. #12
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    =6-d2 - I am uncertain and not able to understand on this. Request your help to make me understand on the same.

    Regards,
    Yadavagiri

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    OK. Back again. I was wrong at Post #10.

    For Hi-Low overall
    =MATCH(C2,PERCENTILE($C$2:$C$33,{1,0.8,0.6,0.4,0.2}),-1)

    For Hi-Low by category (array)
    =MATCH(C2,PERCENTILE(IF(B2=$B$2:$B$33,$C$2:$C$33),{1,0.8,0.6,0.4,0.2}),-1)

    For Low-Hi overall:
    =MATCH(C2,PERCENTILE($C$2:$C$33,{0,0.2,0.4,0.6,0.8}),1)

    For Low-Hi by category (array)
    =MATCH(C2,PERCENTILE(IF(B2=$B$2:$B$33,$C$2:$C$33),{0,0.2,0.4,0.6,0.8}),1)

    Note that in the Low-Hi, the lowest category of quintile has to start at 0.0 and increases in steps of 0.2 and the match type also has to change from -1 to 1.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    WoW. Excellent!! This sorted my longstanding issue and you have been very helpful in assisting me to sort this out. You are a Super Star.

    Also, can you please let me know, if I can update this formula in Pivot. If so, how can use this formula to calculate percentile via Pivot? Please help me in this.

    Regards,
    Yadavagiri

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    You're probably starting another discussion - using array formulae in Pivot Tables. That's probably best handled by opening a new thread. You should include a sample Pivot Table and the formulae that you want to use.

    For the purposes of this thread, I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  16. #16
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Sure, I will raise a new thread for the same. Really much appreciated for your help and I believe you will only assist me in the new thread which I am going to raise in a while and I would be glad if you could only help me since you have helped me at the most expectation that I had.. thank you once again

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    We'll see.... I'm a bit out of practice with Pivot Tables. In a previous job, I was the "King of the Pivot Table", but I haven't had to use one for years....

  18. #18
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Ok... Thanks again for your Help, Glenn... Take care... It was nice interacting with you.!!

  19. #19
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi Glenn,

    Sorry to come back.

    I just tried implementing this formula to my database, however, it doesnt work accurately as Quintile is calculated till Quintile 4 but Quintile 5 is not reflecting. Enclosd attachment, can you please help me on this.

    Regards,
    Yadavagiri

  20. #20
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Quintile Clarification.xlsx

    attached document

  21. #21
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi Glenn,

    Any luck on this please

    Regards,
    Yadavagiri

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Actually, I don't live here and I don't work here, and I don't get paid for these solutions. I do it for fun. So if I am away for 12 hours, or 24, or for a month: that's up to me. I don't like being hassled by sad faces....

    Your problem is that your example data were NOT representative of your real data. Nowhere previously did you mention that there could be non-numerical scores (NA).

    Try this array formula:
    =IFERROR(MATCH(D2,PERCENTILE(IF($D$2:$D$151>0,$D$2:$D$151,""),{1,0.8,0.6,0.4,0.2}),-1),"-")

  23. #23
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Glenn,

    Oh ho.. I am really sorriieeee.. please forgive me... I didn't mean to make you feel bad. Personally, I am not such a person that I hurt anyone.. I am very happy with your responses and solutions which you are giving it to me... please don't my sad faces too personally please. my request to you. Sorry for me being not clear in my request though, but ill ensure I give accurate request to you or in any thread henceforth.

    Sorry once again.

  24. #24
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi Glenn,

    Thanks, the below formula worked.

    =IFERROR(MATCH(D2,PERCENTILE(IF($D$2:$D$151>0,$D$2:$D$151,""),{1,0.8,0.6,0.4,0.2}),-1),"-")

    Can you please give me the formula for Function Wise please if you are ok with that.

  25. #25
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Working file - 1.xlsbHi Glenn,

    In order to close this request in full, you have provided me the below.

    Overall Quintile (Higher to Lower) - Formula - =IFERROR(MATCH(D2,PERCENTILE(IF($D$2:$D$151>0,$D$2:$D$151,""),{1,0.8,0.6,0.4,0.2}),-1),"-") - Array Formula - This is Resolved and no further changes required.

    Overall Quintile (Lower to Higher) - Formula - Need help and formula on this please.

    Function Quintile (Higher to Lower) - Formula - Need help and formula on this please.

    Function Quintile (Lower to Higher) - Formula - Need help and formula on this please.

    Attached my excel working fine.

    PLEASE HELP ME. I WOULD BE HAPPY ON EARTH IF YOU COULD SORT THIS OUT FORME PLEEEEEEEEEEEEEEEEEEEEEEEEEEASE.

    Regards,
    Yadavagiri

  26. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    File attached. Hopefully, that is everything resolved to your satisfaction.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi Glenn,

    Higher to Lower for both Overall & Function wise has been resolved and perfect. Thanks for this.

    However, for Lower to Higher, Quintile is not getting calculation for the scores "0" as its showing as "-" in Quintile column for both Overall & Function. Could you please check this for me? If this is fixed, everything is resolved.

  28. #28
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    OK. Leave it with me for about an hour. Other things to do...

  29. #29
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Silly mistake by me. Fixed now....

    One thing. Filter the data to show enquiries ONLY. Look at column H. Becasue there are so many 0s in this category, there is (at least) one zero that would be in the 4th quintile. Excel (by default) is then putting ALL values of zero into that quintile. In essence all values are placed in the highest possible matching quintile. Is that OK?
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Thanks for this Glenn, I checked your working file which you enclosed above,

    When checked with Overall Quintile wise (Lower to Higher), I could see that there is only Quintile 3, 4, 5
    When checked with Function Wise Quintile - For Enquiries - I can see there is only Quintile 4, 5 but no Quintile 1,2,3, For MO Enquiries there is no Quintile 1.

    I Understand from your above comments, Excel (by default) is putting ALL values of zero into that quintile. In essence all values are placed in the highest possible matching quintile.

    Is there anyway in the formula that we can update to get results calculated in All 5 Quintiles?

    Regards,
    Yadavagiri

  31. #31
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    If the number of "places" in a quintile is (for example) 4 and there are 6 people with a score that should fit into a single quintile, what rule are you going to use to decide who goes in which quintile?

  32. #32
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    I'm not even sure that a rule would make any difference. when you have so many zero vakues, I suspect that what you have is what you have. Ony when fewer values are scored zero, will the problem resolve itself.

  33. #33
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Ok Thank you for your assistance, Glenn.. It was much helpful and much appreciated for your assistance and towards your kind responses.

    For me, Higher to Lower Quintile both "Overall Wise" & "Function Wise" was much helpful and sorted for me as expected, However, for Lower to Higher Quintile, I am bit confused yet as few of the quintile not being calculated accordingly though I agree with your point given above.

  34. #34
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    If you have 100 values; each quintile will contain 20. Assume the 20th highest value = 100. If all 80 of the remining values are zero, the threshold for the quintiles will be 80,0,0,0,0. Excel cannot decide which quintile each zero belongs to: so it puts them in the first quintile that meets the criteria. I think you are stuck with this.

  35. #35
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Yes, Absolutely, I am stuck up with this.

    I don't think there is any other way round. However, the scores of 0 in our database occurs maximum time only for few of the metrics which we have and I believe the excel will calculate based on your explanation above. That is fine, I am fine to go with manual calculation for Lower to Higher Quintile, Glenn..

    Probably, I will need to have a multiplier/addition of numerator put in for these scores where there are "0" and calculate the Lower to Higher Quintile, then it would help me in fixing this problem.

  36. #36
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    OK. Maybe now you can mark the thread as solved.

  37. #37
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Thanks a lot, Glenn, You are truly a Super Star in excel!!!

    You have sorted my query @ the most satisfaction I am glad that I got a fren who could sort this out for me.

    Once again, sorry for the trouble which I caused to you. Please don't it to heart.

    Happy weekend and take care!!

    Cheers!!

    Regards,
    Yadavagiri

  38. #38
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    One last thing. It is possible to force the values into 5 quintiles, based (for example) on the position of a person's name in the Table. See the sheet now. However, if I was near the top of the Table and was placed in quintile 5, with a score of zero, when a colleague at the bottom of the Table was places in quintile 3, also with a score of zero; I would be rather annoyed!!! Especially if it affected my pay/appraisal/job.
    fully randomise allocations to each quintile would require VBA.
    Attached Files Attached Files

  39. #39
    Forum Contributor
    Join Date
    12-16-2015
    Location
    Bangalore
    MS-Off Ver
    MS 365
    Posts
    150

    Re: Quintile Formula - Function Wise - Help need for formula calculation

    Hi Glenn,

    Sorry for the delayed response, I have checked with your attached file, this is perfectly working fine, Really you are a Star.. This has helped me a lot and will be easier for me to calculate Quintile accordingly.

    I see that you have included a hidden column "E" where you have used column name "Modified score" and then you have calculated Percentile formula.

    WoW. It works awesome!!! Thaaaaaaaaaaaaaaaaaaaaaaaaaaank you soooooooooooooooooooooooooooooooo much, Glenn!!!

    Regards,
    Yadavagiri

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to colum wise data to Row wise- data
    By srinivasan1965 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-08-2015, 10:25 AM
  2. [SOLVED] date wise /name wise total with round function
    By hkbhansali in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2014, 11:37 AM
  3. Search Function, ROW WISE Results to COLUMN WISE Results
    By juanl in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-27-2013, 04:07 AM
  4. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  5. Replies: 6
    Last Post: 10-19-2012, 01:27 AM
  6. Data row wise, formula column wise
    By Fred Smith in forum Excel General
    Replies: 4
    Last Post: 12-09-2005, 11:55 AM
  7. Quartile / Quintile Function
    By Greg in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2005, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1