+ Reply to Thread
Results 1 to 11 of 11

AverageIF but for PercentileIF

  1. #1
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    AverageIF but for PercentileIF

    Hi, I'm trying to look for a formula for similar to averageIF but something with percentile, so a percentileIF.

    I got a column with names, and a column with time. I'm trying to find the 80% percentile of the time based on the name. Each of the names contain different times and the names can repeats.

  2. #2
    Forum Contributor
    Join Date
    09-26-2014
    Location
    Moscow, Russia
    MS-Off Ver
    MSE 10, MSE 13
    Posts
    179

    Re: AverageIF but for PercentileIF

    Samuez, good time of day.

    Please provide us a little example of what you have and what numbers do you want to receive in the end.
    Best wishes and have a nice day!

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: AverageIF but for PercentileIF

    The most straightforward way is probably to nest an arrayed IF inside your percentile function.

    Confirm with CTRL+SHIFT+ENTER, nut just ENTER:
    Please Login or Register  to view this content.
    Does that do it for you?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    Re: AverageIF but for PercentileIF

    Hi,

    i attached a file.

    suppose that they are named LEE, we need to find the percentile of LEE only AND with only the time for LEE.
    We can do it manually for this case but we want to apply this for large amount of data,so if you can apply any formula to automate it.


    Sam
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    Re: AverageIF but for PercentileIF

    Hey Ben,
    This does not work.
    It calculates the percentile for whole data,not specifically for a particular name which I need.

    Sam

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: AverageIF but for PercentileIF

    A2:A45 are date+time. Do you want to find the percentile of just the time, or date+time?

  7. #7
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    Re: AverageIF but for PercentileIF

    i need for just time

  8. #8
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: AverageIF but for PercentileIF

    See attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    Re: AverageIF but for PercentileIF

    Hi copy this formula =PERCENTILE.INC(A2:A15,0.8) and this value is for Lee.this is the thing that we want to automate.

    Can you explain middle part of your formula

  10. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: AverageIF but for PercentileIF

    Quote Originally Posted by samuez View Post
    i need for just time
    Quote Originally Posted by samuez View Post
    Hi copy this formula =PERCENTILE.INC(A2:A15,0.8) and this value is for Lee.this is the thing that we want to automate.
    These two statements are contradictory.

    Quote Originally Posted by samuez View Post
    can you explain middle part of your formula
    This part is why...

    OK.

    Excel stores dates as integers counting up from Jan 1, 1900. So
    today = Oct 3, 2014 = 41915.00
    Time is stored as a double from 0.0000 at midnight to 0.5000 at noon then wrapping back around to 0.9999 right before midnight again.

    This is so that you can have date + time added together in one cell.

    That's what you've done in your A2:A145 range. You've got date+time info.

    So for A2 for example:
    you're displaying "1:27 AM"
    but it's acutally "Jan 2nd, 2014, 01:27:00"
    =41641.0604842593

    So if you want to take the percentile of only the times, and drop off the dates, you need to chop off the date information.

    That's what
    A1 - INT(A1)
    does.
    INT(A1) drops everything after the decimal point and truncates the number to an integer.
    41641.0604842593 - INT(41641.0604842593) = 0.0604842593
    which is the time only, without the date.

    Like you asked.

    So,
    ={PERCENTILE.INC( IF($B$2:$B$44=D4, $A$2:$A$44), 80%)}
    and
    =PERCENTILE.INC(A2:A15,0.8)
    will both deliver the same number... but it's not what you actually asked for becaus it's actually 41655.4227250602 = "16-01-2014 10:08:43 AM"

    If you only want to consider the time without also which day is the 80% percentile rank of the days of the range, you have to normalize the times by removing the date info.
    That's why I added the "DATETIME - INT(DATETIME)" operation.

  11. #11
    Registered User
    Join Date
    10-03-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    11

    Re: AverageIF but for PercentileIF

    Oh my god, Thank you. We didn't know that DATE was affecting our percentile THANK YOU!!

+ 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. [SOLVED] help with =AVERAGEIF
    By Joshua Buche in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2014, 06:36 PM
  2. =Averageif help
    By pmdcmatt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2013, 04:53 PM
  3. averageif
    By Ecel12 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-25-2011, 08:21 AM
  4. Averageif
    By wachinnai in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-19-2011, 02:01 PM
  5. Excel 2007 : Averageif
    By PaulMc in forum Excel General
    Replies: 6
    Last Post: 09-29-2008, 03:45 AM

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