how to sum highest ranking values meeting criteria within a limit?

**QuantumPion** · 06-02-2005, 04:24 PM

Here is a more detailed explanation. I need a function that will sum the n largest values in an array, where the values are less then x and are not #N/A, meet a criteria in a seperate array, and where the total of the sum is less then or equal to y. How can I do this? Thanks.

For example, here is my data:

Please Login or Register  to view this content.

I want a function that will find the sum of the largest two values that are "g" that add to 8 or less. So the output would look like:

Please Login or Register  to view this content.

tkt_tang@hotmail.com · 06-02-2005, 10:05 PM

But, 1+7 = 8 (namely, add to 8 or less) ; and so, does the given combo
meet the criterion ?

Regards.

tkt_tang@hotmail.com · 06-02-2005, 10:05 PM

But, 1+7 = 8 (namely, add to 8 or less) ; and so, does the given combo
meet the criterion ?

Regards.

**QuantumPion** · 06-06-2005, 05:40 AM

My problem is trying to make a forumla that adds the highest values that total less then a limit though. What I need is a way to repeat a forumla until a result is achieved, sort of like:

if(sum(rank(1:32)>1500,if(sum(rank(2:33)>1500,if(sum(rank(3:34)>1500, ... ad nausem.

tkt_tang@hotmail.com · 06-07-2005, 10:05 AM

Instead of deploying Sum(Rank(1:32)) ...... ,

you may wish to experiment with :
SUM(LARGE(TargetRange,ROW(INDIRECT(ROW()&":"&ROW()+31)))).

The reference TargetRange is self-evident.

Regards.