Spreadsheet: 599736d1543285453-substracting-untill-value-is-reached-starting-from-highest-rank-onwards-example-workbook
Sheet: Sheet1

In order to sell products, displays are placed in stores. Each store contains 1 display with several products. I want to determine which products that should be, as there is limited spacing |  | Each different product contains different values with regard to Margin, or consumer price. Based on this it is given a rank within that display | This column contains the amount of shelves that can potentially be filled, based on the expected sales of the products (I have left out these numbers as the only relevant information here is the amount of shelves) | Based on the type of display (which I have neglected here due to complexity) there is a specific amount of shelves that can be filled. This column contains the amount of shelves that exceed the maximum spacing in the display. It is a total for the whole store
Data |  |  |  |  | 1st part | 1st part | 1st part | 2st part | 2st part | 2st part result
Store | Product | RANK | Shelves filled | Shelves to be removed | Sumproduct rank >= | Condition <E4 | Result | Sumproduct rank > | Condition <E4 | Result | Preffered outcome (shelves removed) - I need a formula for this column
Store 1 | Product 1 | 2 | 2 | 3 |  |  |  |  |  |  |  | in total 3 shelves need te be removed in store 1 (column F). Product 8 has the highest ranking (column D), but this product doesn't fill up any shelves (column E). Therfore no shelves can be removed. Subsequently product 7 has the highest rank, but doesn't fill up any shelves either. Than product 4 has the highest subsequent ranking; and this product does fill up 1 shelve. As there are 3 shelves to be removed, this is the first of the three. Next highest rank is product 2, which also fills up 1 shelf. Therefore this is the second shelf to be removed. Than product 3 has the highest ranking, and only 1 of the two shelves this product fills needs to be removed in order to reach the total amount of 3 shelves.
Store 1 | Product 2 | 5 | 1 | 3
Store 1 | Product 3 | 4 | 2 | 3
Store 1 | Product 4 | 6 | 1 | 3
Store 1 | Product 5 | 1 | 6 | 3
Store 1 | Product 6 | 3 | 2 | 3
Store 1 | Product 7 | 7 | 0 | 3
Store 1 | Product 8 | 8 | 0 | 3
Store 2 | Product 1 | 1 | 2 | 5 |  |  |  |  |  |  |  | In total 5 shelves need to be removed (column F) in store 2. As the highest 2 ranks (product 7 and 8 do not fill up any shelves (column E). The next highest rank is product 5, which fills up 6 shelves. As 5 shelves need to be removed, I remove 5 of these 6 shelves.
Store 2 | Product 2 | 2 | 1 | 5
Store 2 | Product 3 | 4 | 1 | 5
Store 2 | Product 4 | 3 | 1 | 5
Store 2 | Product 5 | 6 | 6 | 5
Store 2 | Product 6 | 5 | 2 | 5
Store 2 | Product 7 | 7 | 0 | 5
Store 2 | Product 8 | 8 | 0 | 5
Store 3 | Product 1 | 2 | 2 | 4
Store 3 | Product 2 | 5 | 1 | 4
Store 3 | Product 3 | 4 | 1 | 4
Store 3 | Product 4 | 6 | 1 | 4
Store 3 | Product 5 | 1 | 6 | 4
Store 3 | Product 6 | 3 | 2 | 4
Store 3 | Product 7 | 7 | 0 | 4
Store 3 | Product 8 | 8 | 0 | 4
Store 4 | Product 1 | 1 | 2 | 2
Store 4 | Product 2 | 4 | 1 | 2
Store 4 | Product 3 | 13 | 2 | 2
Store 4 | Product 4 | 2 | 1 | 2
Store 4 | Product 5 | 5 | 6 | 2
Store 4 | Product 6 | 8 | 2 | 2
Store 4 | Product 7 | 6 | 0 | 2
Store 4 | Product 8 | 7 | 0 | 2
Store 4 | Product 9 | 9 | 1 | 2
Store 4 | Product 10 | 12 | 6 | 2
Store 4 | Product 11 | 11 | 2 | 2
Store 4 | Product 12 | 10 | 1 | 2
Store 4 | Product 13 | 3 | 0 | 2