Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding 4 smallest numbers greater than zero
I am a high school golf coach making a spreadsheet for my golfers stats using
excell 2007. I need to be able to add the 4 lowest values in a data table that are greater than 0 Ex. 75,76,77,78,79,0,0 are the values. I need a formula that will automaticly ignore the zero's and add the 4 smallest values. In this case it would be 75,76,77,78. Any help would be appreciated! Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding 4 smallest numbers greater than zero
Try this array formula**.
Assuming there are at least 4 numbers 0. =SUM(SMALL(IF(A1:G10,A1:G1),{1,2,3,4})) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Tangyteacher" wrote in message ... I am a high school golf coach making a spreadsheet for my golfers stats using excell 2007. I need to be able to add the 4 lowest values in a data table that are greater than 0 Ex. 75,76,77,78,79,0,0 are the values. I need a formula that will automaticly ignore the zero's and add the 4 smallest values. In this case it would be 75,76,77,78. Any help would be appreciated! Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding 4 smallest numbers greater than zero
Might be able to shorten this somehow, but this array** formula works:
=SUM(SMALL(IF(A2:A100,A2:A10),1),SMALL(IF(A2:A10 0,A2:A10),2),SMALL(IF(A2:A100,A2:A10),3),SMALL(IF (A2:A100,A2:A10),4)) **Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tangyteacher" wrote: I am a high school golf coach making a spreadsheet for my golfers stats using excell 2007. I need to be able to add the 4 lowest values in a data table that are greater than 0 Ex. 75,76,77,78,79,0,0 are the values. I need a formula that will automaticly ignore the zero's and add the 4 smallest values. In this case it would be 75,76,77,78. Any help would be appreciated! Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding 4 smallest numbers greater than zero
Thanks Luke M... I really appreciate your help!
"Luke M" wrote: Might be able to shorten this somehow, but this array** formula works: =SUM(SMALL(IF(A2:A100,A2:A10),1),SMALL(IF(A2:A10 0,A2:A10),2),SMALL(IF(A2:A100,A2:A10),3),SMALL(IF (A2:A100,A2:A10),4)) **Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tangyteacher" wrote: I am a high school golf coach making a spreadsheet for my golfers stats using excell 2007. I need to be able to add the 4 lowest values in a data table that are greater than 0 Ex. 75,76,77,78,79,0,0 are the values. I need a formula that will automaticly ignore the zero's and add the 4 smallest values. In this case it would be 75,76,77,78. Any help would be appreciated! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Script to find smallest number greater than 0 | Excel Discussion (Misc queries) | |||
Identifing smallest numbers | Excel Discussion (Misc queries) | |||
adding equal/greater than into formula | Excel Discussion (Misc queries) | |||
Sum of a row minus two smallest numbers | Excel Worksheet Functions | |||
How to add the 10 smallest numbers out of a range of 20 numbers. | Excel Worksheet Functions |