need function to sum top ranking items in list

Q

QuantumPion

How can I make a function that will sum the largest 32 items in a colum
which meet specific criteria? For example, the largest 32 values tha
are smaller then X, or are not #N/A? I think I want to use SUMIF, bu
how can I format the criteria to evaluate a function for each cell
 
H

Harlan Grove

QuantumPion wrote...
How can I make a function that will sum the largest 32 items in a column
which meet specific criteria? For example, the largest 32 values that
are smaller then X, or are not #N/A? I think I want to use SUMIF, but
how can I format the criteria to evaluate a function for each cell?
Click to expand...

SUMIF only provides one criterion. Use the array formula

=SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW(INDIRECT("1:32"))))
 
B

Bob Phillips

Here is an example that sums the top 32 that are not equal to 35

=SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))

it is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
 
J

JE McGimpsey

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LARGE(IF(IF(ISNUMBER(rng),rng)<X,IF(ISNUMBER(rng),rng,0)),
ROW(INDIRECT("1:32"))))
 
H

Harlan Grove

Bob Phillips wrote...
Here is an example that sums the top 32 that are not equal to 35

=SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))

it is an array formula, so commit with Ctrl-Shift-Enter
Click to expand...
....

If it has to be an array formula, no point using SUMPRODUCT rather than
SUM. Also, LARGE only returns numbers, so no need for the '--' or the
parentheses enclosing the LARGE call.

=SUM(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32"))))
 
J

JE McGimpsey

That fails under the OP's "not #N/A" criterion.

Bob Phillips said:
Here is an example that sums the top 32 that are not equal to 35

=SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))

it is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"QuantumPion" <[email protected]>
wrote in message
Click to expand...
 
J

JE McGimpsey

Harlan Grove said:
QuantumPion wrote...

SUMIF only provides one criterion. Use the array formula

=SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW(INDIRECT("1:32"))))
Click to expand...

Note that returns #N/A if the List contains #N/A...
 
Q

QuantumPion

Here is a more detailed explanation. I need a function that will sum th
n largest values in an array, where the values are less then x and ar
not #N/A, meet a criteria in a seperate array, and where the total o
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 tha
are "g" that add to 8 or less. So the output would look like:


Code
-------------------

name value
alpha 1
foxtrot 7

sum: 8
 
Q

QuantumPion

now that I think about it, this is much more complicated. I hav
multiple "value" columns for different dates. And I need to pick th
highest 32 items that meet the criteria for each date, but none of th
items can be used twice. So an additional criteria is that none of th
32 items could have been used in the previous column. Bleh! I'l
probably have to do this manually
 
H

Harlan Grove

JE McGimpsey wrote...
Note that returns #N/A if the List contains #N/A...
Click to expand...

Did you test that assertion? Apparently not.

Note that I use *2* *nested* IF calls. The first in effect passes only
numbers on to the second. Any #N/A in List (or any other error values,
text or boolean values) will produce a FALSE value in the result array
from the outer IF call.

A simplified example, if A1:A6 contained

1
#N/A
3
4
5
6

and B1 contained the *ARRAY* formula

=SUM(LARGE(IF(ISNUMBER(A1:A6),IF(A1:A6<5,A1:A6)),ROW(INDIRECT("1:2"))))

the formula would return 7, not #N/A, because the formula would
evaluate as

=SUM(LARGE(IF(ISNUMBER({1;#N/A;3;4;5;6}),
IF({1;#N/A;3;4;5;6}<5,{1;#N/A;3;4;5;6})),{1;2}))

=SUM(LARGE(IF({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},
IF({TRUE;#N/A;TRUE;TRUE;FALSE;FALSE},{1;#N/A;3;4;5;6})),{1;2}))

=SUM(LARGE(IF({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},
{1;#N/A;3;4;FALSE;FALSE}),{1;2}))

=SUM(LARGE({1;FALSE;3;4;FALSE;FALSE},{1;2}))

=SUM({4;3})

=7
 
H

Harlan Grove

JE McGimpsey wrote...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LARGE(IF(IF(ISNUMBER(rng),rng)<X,IF(ISNUMBER(rng),rng,0)),
ROW(INDIRECT("1:32"))))
Click to expand...
....

IF X is a number, the 3rd IF call, IF(ISNUMBER(rng),rng,0), is
unnecessary and should be replaced with just rng. Note that this relies
on the obscure fact that FALSE evaluates greater than any number value.
 
J

JE McGimpsey

Actually I did, but I can't replicate it - must have been the same
problem that caused me to add the extra if() in my formula.

Sorry!
 
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.
 
Top