Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Top 90% of records - Bring out number
Have the oddest SLA requirement I've ever been asked to have a look at,
hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 45th record). Hope this makes sense, any questions please let me know. Thanks, Al (mralmackay) |
#2
|
|||
|
|||
Is it as simple as this?
Assuming that the data is in column A =OFFSET($A$1,round(COUNTA($A:$A)*90%,0)-1,0) I put 1-50 in A1:A50 and this returned 45. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 45th record). Hope this makes sense, any questions please let me know. Thanks, Al (mralmackay) |
#3
|
|||
|
|||
Thanks Bob, this appears to work brilliantly.
Any chance you could explain the formula? TIA, Al. Bob Phillips wrote: Is it as simple as this? Assuming that the data is in column A =OFFSET($A$1,round(COUNTA($A:$A)*90%,0)-1,0) I put 1-50 in A1:A50 and this returned 45. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 45th record). Hope this makes sense, any questions please let me know. Thanks, Al (mralmackay) |
#4
|
|||
|
|||
Blimey, I've forgotten myself by now :-)
Seriously, COUNTA($A:$A) simply counts how many filled cells in column A ROUND(...*90%,0) multiplies that number by 90% and rounds it, in case there are say 52 rows OFFSET($A$1,...-1,0) simplys gets the cell that is the 90% number less 1 rows away from A1(we subtract 1 as OFFSET gets the number of rows away, not the nth row) -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Thanks Bob, this appears to work brilliantly. Any chance you could explain the formula? TIA, Al. Bob Phillips wrote: Is it as simple as this? Assuming that the data is in column A =OFFSET($A$1,round(COUNTA($A:$A)*90%,0)-1,0) I put 1-50 in A1:A50 and this returned 45. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 45th record). Hope this makes sense, any questions please let me know. Thanks, Al (mralmackay) |
#5
|
|||
|
|||
Thanks Bob,
Really appreciated. Cheers, Al. Bob Phillips wrote: Blimey, I've forgotten myself by now :-) Seriously, COUNTA($A:$A) simply counts how many filled cells in column A ROUND(...*90%,0) multiplies that number by 90% and rounds it, in case there are say 52 rows OFFSET($A$1,...-1,0) simplys gets the cell that is the 90% number less 1 rows away from A1(we subtract 1 as OFFSET gets the number of rows away, not the nth row) -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Thanks Bob, this appears to work brilliantly. Any chance you could explain the formula? TIA, Al. Bob Phillips wrote: Is it as simple as this? Assuming that the data is in column A =OFFSET($A$1,round(COUNTA($A:$A)*90%,0)-1,0) I put 1-50 in A1:A50 and this returned 45. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Have the oddest SLA requirement I've ever been asked to have a look at, hope you can help. Basically have a set of data of closed records for 2005. My manager wants to show of the closed records how many days were 90% of them completed? She is looking at this as a 'best case' scenario, so therefore in simplistic terms this means: If there were 50 records closed, look at 90% of these records (in shortest amount of days order) and give the highest number once at 90%. e.g. (with a small dataset). There are 50 records. The largest number of the dataset once sorted is 20 (at the 45th record). Hope this makes sense, any questions please let me know. Thanks, Al (mralmackay) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
making a number be text | Excel Worksheet Functions | |||
Maximum number of rows? | Excel Discussion (Misc queries) | |||
Number of records by Month that meet a specific requirement | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |