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!

Try this array formula**.

Assuming there are at least 4 numbers >0.

=SUM(SMALL(IF(A1:G1>0,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" <(E-Mail Removed)> wrote in message
news:CA4F74F3-D67B-4D42-B1F5-(E-Mail Removed)...
>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!

Might be able to shorten this somehow, but this array** formula works:

=SUM(SMALL(IF(A2:A10>0,A2:A10),1),SMALL(IF(A2:A10>0,A2:A10),2),SMALL(IF(A2:A10>0,A2:A10),3),SMALL(IF(A2:A10>0,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!

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:A10>0,A2:A10),1),SMALL(IF(A2:A10>0,A2:A10),2),SMALL(IF(A2:A10>0,A2:A10),3),SMALL(IF(A2:A10>0,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!