[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
javatexan
Newbie

Joined: 12 May 2009
Posts: 2

 Posted: Tue May 12, 2009 6:44 am    Post subject: calculating grades Here is an interesting one that I need help with pronto. g1 g2 g3 g4 g5 g6 g7 dropgrade gradeAvg stu1 95 98 45 34 23 98 76 1 stu2 78 45 98 97 55 99 25 2 stu3 99 99 99 99 99 99 25 2 =SUMIF(D2:Q2;">="&LARGE(D2:Q2;COUNTA(D2:Q2)-R2)) I trying to use this to get the top 7 or 8 values from a range of values but it is getting all of the values over the value. I strictly just want the 7 largest from the list to be summed together....it is of course summing all values over the nth value. so when I try to get the average of these grades, it comes out wacked out. i don't think sumif and large will work anyway, because even if you use a count to control duplicates, it will take the first n numbers it finds in the list that are bigger, not the biggest. Am I interpreting that correct? Is there a way in calc to sort in place and sum the top 7 or 8 values? Obviously I cant sort these or I wont know what stu2 did on assignment 3, right? Thanks._________________-javatexan
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Tue May 12, 2009 8:12 am    Post subject: But you are only showing 7 grades. Do you mean you have more [D2:Q2]? Do you want the top 7 OR the top 8? First you seem to suggest both then just the one. What is the "dropgrade" item? I don't see the connection if you are dropping many grades to retain only the top 7. Does it refer to the lowest grade as shown? It doesn't seem to. Will you be accounting for "empty" grade cells? .... Consider just the LARGE() function...Data in column A: =LARGE(A1:A30;1)+LARGE(A1:A30;2)+LARGE(A1:A30;3)+LARGE(A1:A30;4)+LARGE(A1:A30;5)+LARGE(A1:A30;6)+LARGE(A1:A30;7)
javatexan
Newbie

Joined: 12 May 2009
Posts: 2

 Posted: Tue May 12, 2009 8:57 am    Post subject: Yeah...I just made a very quick example that is much smaller. I generally have ~20 scores and eventually I want to drop, say the lowest 1 or 2. I am looking for a better way to automate where I just add the number I want to skip for a region and calc figures the rest. What you have is what I want to do just I would like to see it grow for the number of entries in a range instead of doing it by hand. I hope this helps. Thanks._________________-javatexan
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue May 12, 2009 9:17 am    Post subject: =LARGE(range;COLUMN(A1)) copy to the right_________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum