I am trying to calculate the average of the lowest 6 numbers in an array. I do not wish to include 0. I have tried a SMALL formula, which works, but I cannot exclude the zeros. I have tried IF and COUNT IF, but can't get them to work. Any advice welcome.
I suspect you need to copy the data into a second array in a way which omits the zeros, unless you can omit zeros from the original array and leave those cells blank.
tt2
How would I go about doing that - I have over a hundred rows to deal with.
I have seen that it is possible in Excel using a combination of IF and SMALL =SUM(SMALL(IF(F5:F36<>0,F5:F36),{1,2,3,4,5,6,7,8,9,10}))/10
but this doesn't work on Numbers.
Hi DiggleA,
This will do what you are trying to do. I am only looking at the lowest 2 (excluding 0) in my example.
I think there must be a more elegant way. See if I have time to look for it.
quinn
You can replicate the values on another part of the sheet, or a different sheet, somewhat like this:
tt2
Count the number of zeros in the range and add that onto the offset for the 1st smallest value, and repeat. That works.
tt2
Thank you - I have cracked it.
Here's an easier to read formula set made by breaking the process into three components:
Data is recorded on the Data table to the left. All calculations are done on the Average table to the right.
A2: =COUNTIF(Data::A,0)
B2, filled to B7: =SMALL(Data::A,ROW()−1+$A$2)
B8 (row 8 is a Footer Row): =AVERAGE(B)
Should you ever want to include more (or fewer) than 6 values in the average, all that is required is to add rows to the body of the Average table (or delete body rows from the table). Numbers will djust the extraction formula as needed. The other two formulas will rmain the same as they are.
Regards,
Barry
Thanks everyone!
NUMBERS -Using SMALL and IF to exclude 0
