• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find number of rows that meets cumulative percentage critieria

M

miconi

New Member
I want to calculate weighted median so i need to find number of rows that meets cumulative percentage criteria (<=50.0%). I have two columns, with sorted values and corresponding weights. My calculation of weighted median of values will be in the same row values where first cell of weights reach cumulative percentage criteria (<=50.0%). I can do this manually in another column, but I would like to make it automatic in one cell because it is a part of large datebase, with lot of filters and different arrays.


Thanks in advance
 
That was a bit hard to visualize. You could paste/post a small sample of what the data layout looks like, and example of what you'd like to see with example data?
 
https://www.dropbox.com/s/xbvi0ks2uudo15h/Sample%20book%20-%20%20number%20of%20row%20that%20meet%20cumulative%20perentage%20criteria.xlsx


I would like just put sum "magic" formula to search this row, not to calculate weighted median in aditional column...
 
Miconi


Firstly, Welcome to the Chandoo.org Forums


Instead of using =OFFSET(D3,COUNT(D4:D809),0) in F3 you could use a simple =Max(D:D)


I'm more concerned about the weighting

In typical weighting you would multiply your value x weight and divide by the sum of weights to get the weighted average

In your case =SUMPRODUCT(A4:A809,B4:B809)/SUM(B4:B809)

= 14.43105


Not sure if either of the above help
 
Hello Miconi,


Try this Array Formula, with CTRL+SHIFT+ENTER.


=INDEX(A4:A809,MATCH(SUM(B4:B809)*50%,SUMIF(OFFSET(B4,,,ROW(B4:B809)-ROW(B4)+1),"<9E+300")))


You could also use SUBTOTAL(9 in place of SUMIF.


Hope this helps,

Haseeb
 
Hi all,

I would like to thanks for trying to help me.

@ Hui

This is a very good point Hui, but on this data from survey it is better to use median than mean. Actually this data shows full sample of country survey, and weights are here just to give representativeness for this sample on the country level.


@ Haseeb

It is very elegant solution Haseeb.

Thank you a lot. This formula works correct, especially with subtotal(9,

I am not very familiar with this criteria "<9E+300" so would you be so kind to explain me what is it represents?
 
You must log in or register to reply here.
Back
Top