Forum Discussion

micyano's avatar
micyano
Copper Contributor
Nov 29, 2023

Sum the top x% of data

Hello friends,

 

I have a dynamic table array that will display multiple columns and rows of data.  I need a way to parse this data and only sum the top (say 20%, this number might change) of the data.  Elsewhere on the sheet I have a total for the number of sections, as well as the height at the top.

The number of rows in the table will change (AKA the number of sections is dynamic), as will the total height.

 

 

Thanks for reading

 

Michael

 

 

excel
Formulas and Functions

1 Reply

Sort By
  • michyano's avatar
    michyano
    Copper Contributor
    Nov 29, 2023

    I've just realized that I need the top X% based on the total height of the sections, not the number of sections, as some sections could be longer or shorter than others.  Also, I could easily combine the two columns of weights into another column if that would make it easier

     

    Thanks Again,

     

    Michael

     

    I figured it out, I made a reference cell which is the percent I want it to check (in this case 20%), and filled it in as 0.20, and another reference cell that is 1 minus that cell multiplied by the max height, giving us a threshold to test to. Then I made an additional column, which is an IF statement that checks the top of the section versus the threshold value, if it is larger, then it adds the two corresponding weight columns.  I then just have to sum that column to get the answer.

Resources