 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / April 2008Mean of Values EXCLUDING two lowest values
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Gradebook help question. I need to calculate the mean of several scores after dropping the two lowest scores. I found the MIN function, but cannot determine how to omit the TWO lowest. Thank you in advance. One way: =SUM(rng,-SMALL(rng,{1,2})) or, equivalently (array-entered with CMD-RETURN): =SUM(LARGE(rng, ROW(INDIRECT("1:"&COUNT(rng)-2)))) > Version: 2008 > Operating System: Mac OS X 10.5 (Leopard) [quoted text clipped - 4 lines] > determine how to omit the TWO lowest. > Thank you in advance. I have tried this formula with fictitious grades and everything worked wonderfully, but when I apply it to my nonconsecutive range - which contains zeros, it gives me an error. Thanks for this - it really opened a door for me to consider. If you are able to help with my current problem described above, I have attached the formula as I entered it. All are postive integers except for U3 which has a value of zero (0). =(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400 Thanks again. On Apr 29, 8:29 pm, acpha...@officeformac.com wrote: > =(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400 Would you need a separate set of parentheses around your P3,R3, ... (etc)? PhilD -- <>< > I have tried this formula with fictitious grades and everything worked > wonderfully, but when I apply it to my nonconsecutive range - which contains [quoted text clipped - 6 lines] > > =(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400 One way (array-entered: CMD-RETURN): =SUM(P3:AD3, -SMALL(IF(ISNA(MATCH(COLUMN(P3:AD3), {16,18,21,24,27,30},0)), "", P3:AD3), {1,2})) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.