Excel question 'deciling'

%3CLINGO-SUB%20id%3D%22lingo-sub-2801391%22%20slang%3D%22en-US%22%3EExcel%20question%20'deciling'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801391%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20wondering%20if%20anyone%20can%20help%20me%20with%20an%20excel%20script..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20capturing%20a%20name%20(column%20A)%20and%20number%20(column%20B)%20in%20descending%20order%20from%20greatest%20to%20least%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethese%20are%20blinded%20doctors%20and%20their%20patient%20number%20(I%20attached%20dummy%20data%20for%20reference)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20for%20a%20formula%20(which%20would%20go%20in%20column%203)%20which%20will%20sum%20the%20entire%20set%20(42%2C387)%2C%20divide%20it%20by%2010%20(4238.7)%20(this%20will%20provide%20how%20many%20patients%20belong%20in%20each%20'decile')%20%2C%20then%20be%20able%20to%20add%20up%20the%20number%20of%20rows%20it%20takes%20to%20reach%20the%20decile%20(AKA%20how%20many%20rows%20to%20reach%20and%20not%20surpass%204238.7)...then%20every%20set%20of%20names%20is%20labeled%2010%2C%209%2C%208...%20all%20the%20way%20to%201%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20your%20thoughts%20and%20thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EK%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2801391%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2802166%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20question%20'deciling'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1171867%22%20target%3D%22_blank%22%3E%40kenhoegl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C2%2C%20enter%20the%20start%20value%2010.%3C%2FP%3E%0A%3CP%3EIn%20C3%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DC2-(SUM(%24B%242%3A%24B3)%26gt%3B(11-C2)*%24F%244)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFill%20down%20to%20the%20last%20row%20with%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am wondering if anyone can help me with an excel script..

 

I have a spreadsheet capturing a name (column A) and number (column B) in descending order from greatest to least 

 

these are blinded doctors and their patient number (I attached dummy data for reference)

 

I am hoping for a formula (which would go in column 3) which will sum the entire set (42,387), divide it by 10 (4238.7) (this will provide how many patients belong in each 'decile') , then be able to add up the number of rows it takes to reach the decile (AKA how many rows to reach and not surpass 4238.7)...then every set of names is labeled 10, 9, 8... all the way to 1 

 

Let me know your thoughts and thank you

 

K

 

 

 

 

 

1 Reply

@kenhoegl 

In C2, enter the start value 10.

In C3, enter the formula

=C2-(SUM($B$2:$B3)>(11-C2)*$F$4)

Fill down to the last row with data.