tag:blogger.com,1999:blog-2509167754737584590.post23291451363166412..comments2020-03-30T20:36:01.391+05:30Comments on Yogesh Gupta's Excel Tips: Excel Formula : Sum top 5 in unsorted rangeYogesh Guptahttp://www.blogger.com/profile/08246342663404873564noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-2509167754737584590.post-45294510885261430302016-03-02T16:00:53.197+05:302016-03-02T16:00:53.197+05:30Very nice explanation of excel formulas.Very nice explanation of excel formulas.Anonymoushttps://www.blogger.com/profile/06352722828865029775noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-66173991342227288272014-12-14T05:11:32.269+05:302014-12-14T05:11:32.269+05:30Hi
I have one small query in addition to this top...Hi<br /><br />I have one small query in addition to this topic. If I have 4 values like<br /><br />Marks obtained Total Marks<br />8 10<br />15 20<br />12 15<br />18 30<br /><br />My question is:<br /><br />First find the best three marks (as the total marks of each assignment (i,e out of marks like 10, 20, 15, 30) are different and obviously the % of each assignment would decide which are best three to choose)<br /><br />Then those three marks have to sum together (based on the three largest % marks) and then get the final % of the sum out of total marks (corresponding)?<br /><br />BACKGROUND:<br /><br />As students gave me the assignments 4 and all assignments have different total marks (i,e out of marks like 10, 20, 15, 30). Now I want first to select the best three assignment marks (which is just possible by calculating their % of each as total marks are different). Then sum those three marks and then get the % of them by using the corresponding total marks of each of them.<br /><br />Your kind reply would highly be acknowledged.<br />Best regards<br />RazaDr. Mirzahttps://www.blogger.com/profile/14254681900776050338noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-2284433178461413172014-06-30T11:24:19.514+05:302014-06-30T11:24:19.514+05:30Thanks Yogesh. Found very helpful and learnt two n...Thanks Yogesh. Found very helpful and learnt two new functions LARGE and ROW.pankajhttps://www.blogger.com/profile/14120804826748321718noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-64672722970118113082013-07-18T06:34:20.610+05:302013-07-18T06:34:20.610+05:30=SUMPRODUCT(LARGE(A2:A100,ROW(INDEX(A:A,1):INDEX(A...=SUMPRODUCT(LARGE(A2:A100,ROW(INDEX(A:A,1):INDEX(A:A,B2))))<br /><br />SUMPRODUCT<br /><br />...where B2 contains the number 3, indicating that you want to sum the top 3 numbers.Anonymoushttps://www.blogger.com/profile/06540141319161926205noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-51711547989159457792013-04-12T07:28:55.711+05:302013-04-12T07:28:55.711+05:30love this formula. thanks for sharing!love this formula. thanks for sharing!Stevehttps://www.blogger.com/profile/14897463123265926580noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-38853432350716154662012-03-13T19:12:33.343+05:302012-03-13T19:12:33.343+05:30I need to get top 3 positions in result sheet. I h...I need to get top 3 positions in result sheet. I have a columns with Total Marks in which I sum all subjects marks Now I want to get top 3 positions from this column please guide me.RaheelQamarhttps://www.blogger.com/profile/07253333896509589695noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-35578811339586655512010-05-02T09:08:58.706+05:302010-05-02T09:08:58.706+05:30@ SmprFidels
Formula will not work the way you ha...@ SmprFidels<br /><br />Formula will not work the way you have constructed it. How ever if your data has less then 5 numbers you can use sum instead of sumproduct as above. This can be handled using if function.<br /><br />I would have deployed following formula in situation like yours<br /><br />=IF(COUNT(E9:N9)<5,SUM(E9:N9),(SUMPRODUCT(LARGE(E9:N9,ROW(1:5)))))<br /><br />I hope this helps, let me know in case you need further help<br />RegardsYogesh Guptahttps://www.blogger.com/profile/08246342663404873564noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-1914062024310046922010-04-30T07:13:02.925+05:302010-04-30T07:13:02.925+05:30What if all the cells in your data range don't...What if all the cells in your data range don't have data yet? is there a way to only total the ones that are there up to a certain number? I tried using something like =SUMPRODUCT(LARGE(E9:N9,ROW(INDIRECT("1:B2"))))<br />where B2 is a cell containing =COUNTIF(E9:N9,">0") to count how many data points exist at this point.SmprFidelshttps://www.blogger.com/profile/14098426782190303802noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-43272262982256646032010-02-13T08:14:19.853+05:302010-02-13T08:14:19.853+05:30Hello kemas
Thanks for your inpputs. I use sumpro...Hello kemas<br /><br />Thanks for your inpputs. I use sumproduct as workaround to enter CSE(Array) Formulas. The problem with the CSE formulas is exactly the same as you made mistake while writing this comment. In case user does not enter it with Ctrl+Shift+Enter it does not work.<br /><br />Problem gets compounded when some one using your worksheet tries to modify your CSE formula. If that user is not aware of CSE formulas, he will never figure out why the formula stoped working.<br /><br />Just write Sumproduct(Your CSE formula) all these issues disappear<br /><br />ThanksYogesh Guptahttps://www.blogger.com/profile/08246342663404873564noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-32173166928539817742010-02-13T01:24:39.061+05:302010-02-13T01:24:39.061+05:30sorry!
Ctrl +Shift+ Entersorry!<br />Ctrl +Shift+ Enterعبد الفتاح كيرةhttps://www.blogger.com/profile/08699660334015956023noreply@blogger.comtag:blogger.com,1999:blog-2509167754737584590.post-78218727600395981412010-02-13T01:23:38.118+05:302010-02-13T01:23:38.118+05:30we can use
=SUM(LARGE(Data;ROW(1:5)))
and click
Ct...we can use<br />=SUM(LARGE(Data;ROW(1:5)))<br />and click<br />Ctrl + Enterعبد الفتاح كيرةhttps://www.blogger.com/profile/08699660334015956023noreply@blogger.com