How to find number of rows that meets cumulative percentage critieria

Hello. I have a dataset in Excel 2007 where I need to determine the number of census tracts (as rows) it takes for each column of data values (populations of different ethnic groups) to reach a cumulative percentage of 50% of their City group total. This is a measure of concentration that will vary from group to group.

I can do this manually by sorting each column from largest population percentage to smallest population percentage, then shift-click selecting the first row, scrolling down until I see the sum of percentages shown in the bottom right corner first reaches 50.000% or more.

Is there a cell formula I can use to determine the number of rows it takes to meet the criteria? I have to do this for 250 columns.

Is there a cell formula I can use to determine the number of rows it takes for a column of data values to reach a specific amount, that is first reach a cumulative percentage of 50.000% of the column total? It must be based on the data values from largest to smallest but I don't want to have to manually sort each column I have.

I need to do this for 250 columns so any help to make this easier would be appreciated.

You could probably create a "count" column and then using an if statement/offset combination.

It would help if you could attach a small sample of your data in format compatible with excel 97-2003

Originally Posted by Domenic

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=MATCH(TRUE,MMULT((ROW(E5:E19)>=TRANSPOSE(ROW(E5:E19)))+0,LARGE(E5:E19,ROW(INDIRECT("1:"&ROWS(E5:E19)))))/E4>=50%,0)

Hope this helps!

There's that MMULT/TRANSPOSE combination again Hey Domenic, how's it going?

Originally Posted by Moatasem91

Could you please advance how can I make it return the sum of value which represent more than 50% rather than the count.

Since it is other people's thread, any further question, try to open a new one.

Originally Posted by Moatasem91

Thanks very much !! this is very helpful.

Could you please advance how can I make it return the sum of value which represent more than 50% rather than the count.

Thanks so much.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

It's probably the INDIRECT() function within the formula causing that...since it is considered a volatile function (ie. recalculates after any change in sheet).

You can turn off calcs by going to Tools|Options >> Calculation tab and selecting Manual..

Then when you want to perform sheet calculations, you will need to hit F9 to recalculate.

Hmm... should work... perhaps it works differently in 2007.... anybody know?