#1
|
|||
|
|||
Simplify "If + SUM + LARGE"?
Code:
{=IF(COUNT(C1:I1)>=5,SUM(LARGE(C1:I1,{1,2,3,4,5})),IF(COUNT(C1:I1)=4,SUM(LARGE(C1:I1,{1,2,3,4})),IF(COUNT(C1:I1)=3,SUM(LARGE(C1:I1,{1,2,3})),IF(COUNT(C1:I1)=2,SUM(LARGE(C1:I1,{1,2})),IF(COUNT(C1:I1)=1,SUM(LARGE(C1:I1,1)))))))} Count how many mark entries (max. = 7, min. = 1). If more than 4, count best 5. If equal to or fewer than 4, count the total (if 4 entries, count best 4; 3, count best 3 ...) "Count best 5" means to sum the best 5 mark entries. Is there any way to simplify the codes? |
#2
|
||||
|
||||
An expression ike
IF(COUNT(C1:I1)=4,SUM(LARGE(C1:I1,{1,2,3,4} means there are only 4 numbers in the range, so, why the LARGE function? As I understand it =IF(count(C1:I1)>=5,sum(large(C1:I1,{1,2,3,4,5})), sum(c1:I1)) should do? (entered as array of course)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
@Pecoflyer #2
{1,2,3,4,5}, {1,2,3,4}, {1,2,3}... Why? It is to cater for the case where there are only 4, 3, 2 or even 1 numbers (not 5). But now your expression appears to be more precise. Thanks for your idea, which inspires me to how to make codes simpler. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas | jocke321 | Word VBA | 2 | 12-10-2014 11:27 AM |
How to edit the "Format" and the "show level" of an EXISTING table of content? | Jamal NUMAN | Word | 2 | 08-14-2011 10:46 AM |
How to choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
"Microsoft Excel Application" missing in the "Component Services" on win08 | sword.fish | Excel | 0 | 02-26-2010 02:09 PM |