Find row that is 75% of filtered list [SOLVED]

[FondaUk]

Hi all,


I have a large list that needs to be filtered based on certain criteria, and of that filtered list, I need to find which row is a 75% of the total filtered row count.


An example:


A list with approx 6000 rows in with columns of ID, Type, Date, Number of Days


The list needs to be filtered with column B with "Type 2", Column C with dates in the range 01 Oct 2012 - 31 Oct 2012.


I have two cells with the date period in and a cell for the Type value, a COUNTIFS for the total number of rows using these criteria and a formula for calculation the 75% value


From this filtered list, (which in my data sample, there are 96 rows) I need to find the value the Column D, Number of days in row 72 of that filtered list - 72 being 75% of 96.


I have a feeling I need to use the RANK function combined with a look up or may be a need to add some more columns into the list


Any suggestions?


Many thanks


Graham

 

[Montrey]

Try this.

=INDEX(D:D,ROUND(COUNT(D:D)*0.75,0))

 

[FondaUk]

Yes, that will give the row, but how to do that on a filtered list....so where would the COUNTIFs statement be in that formula?


One thing I forgot to mention, the filtered list would need to be sorted in ascending order on the Number of Days column

 

[nazmul_muneer]

Dear FondaUK,

Why do you want to use countifs? Please make clear.

Would you upload a sample file?


Regards


Muneer

 

[Montrey]

Ok, well my formula figures out the last part of your question which I originally thought was the only question. Way you worded the question made it seem like you did all the previous stuff yourself. So it is safe to assume, all you have right now is a list of 6000 rows.


So I'm trying to figure out what you have done and haven't done.

My formula finds the value or text string in the row that is 75% of the total rows.

 

[SirJB7]

Hi, FondaUk!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


As yet requested, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!

 

[FondaUk]

workbook at this link


https://docs.google.com/open?id=0ByySPiNwaEgwSzJxY2U5SzdHRms


I need to find the value from column D where the row is 75% of the filtered list where there is a figure in column D of the filtered list and is in ascending order in column D

 

[Haseeb A]

Hello Graham, assuming you are using Excel 2007 or later.


Try this Array Formula in J7, with CTRL+SHIFT+ENTER NOT just ENTER.

=MATCH(2,IF(COUNTIFS(OFFSET($B2,,,ROW($C2:$C6698)-ROW($C2)+1),J$4,OFFSET($C2,,,ROW($C2:$C6698)-ROW($C2)+1),">="&$J$1,OFFSET($C2,,,ROW($C2:$C6698)-ROW($C2)+1),"<="&$J$2)<=J6,IF($B2:$B6698=J$4,IF($C2:$C6698>=$J$1,IF($C2:$C6698<=$J$2,1)))),1)

J8, with just ENTER


=INDEX($D2:$D6698,J7)


Then copy J7:J8 & paste across.


Note: If you have huge range, may take couple seconds to calculate.


Hope this helps,

Haseeb

 

[FondaUk]

Perfect! Many thanks. One quick question though, why the 2 at the beginning of the MATCH statement? I understand the rest of the formula, just not that part...