How do I get numbers to add the 5 smallest numbers out of 8 when there are two of the same number? I'm trying to use the small function and it will not count the duplicate number.
iMac (2017 – 2020)
This is pretty easy to do using SUM() and SMALL()
SMALL() returns the nth smallest number in a set, so:
=SMALL(B1:B20,5)
would return the 5th smallest number.
To get a range of numbers, enter them as an array (requires Numbers 14.4?)
=SMALL(B1:B20,{1,2,3,4,5}))
would return the smallest 5 values, including duplicates.
Now you can SUM them:
=SUM(SMALL(B1:B20,{1,2,3,4,5}))
This is pretty easy to do using SUM() and SMALL()
SMALL() returns the nth smallest number in a set, so:
=SMALL(B1:B20,5)
would return the 5th smallest number.
To get a range of numbers, enter them as an array (requires Numbers 14.4?)
=SMALL(B1:B20,{1,2,3,4,5}))
would return the smallest 5 values, including duplicates.
Now you can SUM them:
=SUM(SMALL(B1:B20,{1,2,3,4,5}))
If you want to sum more than a few of the smallest values, not just the smallest 5, you can use SEQUENCE to make the array of consecutive numbers. Instead of {1,2,3,4,5} you can use SEQUENCE(5,1,1) and the "5" can be changed to a larger number.
Thanks! For some reason, I had a rogue zero in there and it was causing the issue.
Just to make sure about what you want to get when there are duplicates, is this it?
If your numbers are 1,2,3,3,4,4,4,5,6,... you want to sum 1,2,3,3,4 (i.e., sum the 5 smallest numbers)
12345
12345678
Adding the 5 lowest numbers out of 8 entries when there are duplicate numbers
