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


Q

QuantumPion

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:


Code:
--------------------

name type value

alpha g 1
bravo 5
charlie g 2
delta g #N/A
echo 3
foxtrot g 7

--------------------


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:


Code:
--------------------

name value
alpha 1
charlie 2

sum: 3
 
Ad

Advertisements

T

tkt_tang

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

Regards.
 
T

tkt_tang

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

Regards.
 
Q

QuantumPion

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.
 
Ad

Advertisements

T

tkt_tang

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top