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)

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)

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)

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)

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)