Simplify "If + SUM + LARGE"?

tinfanide · #1 07-15-2016, 06:13 AM

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)))))))}

The codes are like this:
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?

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