Saturday, September 8, 2012

yogi_Extract Sum Of Top1 Through Top5 Amount Respectively Of Each Group


Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #740   Sep 08, 2012     www.energyefficientbuild.com.

use meph2u said: (http://productforums.google.com/forum/#!mydiscussions/docs/jLvJYM7FkfI)
Calculating a sum of the top values in the groups. 
https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdGlMcTlIVnhZUjJ3NHYxcnpTTWlCMGc#gid=0 
I have sorted the data by amount within group for illustration purposes, but the data would normally be available in transaction order (or random order).
1) is there a way to calculate the values in J2 (through N2) using only the data in columns B and D regardless of how the data is sorted (if at all)?
2) is there a way to calculate the values in E regardless of how the data is sorted (if at all)?

----
So if there is a way to calculate that on based on B&D, then the rest works great.
If it can all be done in one pass, even better.

--------------------------------------------------------------------------------------
following is a solution to the problem

in response to additional questions from use meph2u, I have provided the sum of Top1 through Top 5 Amount Respectively of each group and also formula for computing a sequecnce of 1 through 5 for each group in Sheet2

and a more generalized formula and an illustration for sequencing of Top 1 through Top how many? and also the sum of Top1 through how many? in Sheet3