Home |
Search |
Today's Posts |
#1
|
|||
|
|||
need function to sum top ranking items in list
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? -- QuantumPion ------------------------------------------------------------------------ QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991 View this thread: http://www.excelforum.com/showthread...hreadid=376101 |
#2
|
|||
|
|||
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? SUMIF only provides one criterion. Use the array formula =SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW( INDIRECT("1:32")))) |
#3
|
|||
|
|||
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" wrote in message ... 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? -- QuantumPion ------------------------------------------------------------------------ QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991 View this thread: http://www.excelforum.com/showthread...hreadid=376101 |
#4
|
|||
|
|||
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")))) In article , 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? |
#5
|
|||
|
|||
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 .... 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:3 2")))) |
#6
|
|||
|
|||
That fails under the OP's "not #N/A" criterion.
In article , "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 -- HTH Bob Phillips "QuantumPion" wrote in message ... 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? |
#7
|
|||
|
|||
In article .com,
"Harlan Grove" wrote: 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? SUMIF only provides one criterion. Use the array formula =SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW( INDIRECT("1:32")))) Note that returns #N/A if the List contains #N/A... |
#8
|
|||
|
|||
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: 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 foxtrot 7 sum: 8 -------------------- -- QuantumPion ------------------------------------------------------------------------ QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991 View this thread: http://www.excelforum.com/showthread...hreadid=376101 |
#9
|
|||
|
|||
now that I think about it, this is much more complicated. I have multiple "value" columns for different dates. And I need to pick the highest 32 items that meet the criteria for each date, but none of the items can be used twice. So an additional criteria is that none of the 32 items could have been used in the previous column. Bleh! I'll probably have to do this manually. -- QuantumPion ------------------------------------------------------------------------ QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991 View this thread: http://www.excelforum.com/showthread...hreadid=376101 |
#10
|
|||
|
|||
JE McGimpsey wrote...
"Harlan Grove" wrote: .... SUMIF only provides one criterion. Use the array formula =SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),RO W(INDIRECT("1:32")))) Note that returns #N/A if the List contains #N/A... 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)),R OW(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 |
#11
|
|||
|
|||
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")))) .... 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. |
#12
|
|||
|
|||
True. Thanks for the correction!
In article .com, "Harlan Grove" wrote: 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. |
#13
|
|||
|
|||
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! In article .com, "Harlan Grove" wrote: Did you test that assertion? Apparently not. |
#14
|
|||
|
|||
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(s um(rank(3:34)1500, ... ad nausem. -- QuantumPion ------------------------------------------------------------------------ QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991 View this thread: http://www.excelforum.com/showthread...hreadid=376101 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I filter a list using a greater than todays date function? | Excel Worksheet Functions | |||
clock | Excel Worksheet Functions | |||
i need a function to find the highest value in a list | Excel Worksheet Functions | |||
Items in a List | Excel Discussion (Misc queries) | |||
If function using a list of criteria | Excel Discussion (Misc queries) |