Discarding zero's

  • Thread starter Thread starter @Homeonthecouch
  • Start date Start date
@

@Homeonthecouch

Hello
I have a set of cells D3:X3, Some contain data, some as yet Don't
I want to add the lowest 5 numbers in these cells but do not want to count
the zero's
=SUM((SMALL(D3:X3,1),SMALL(D3:X3,2),SMALL(D3:X3,3),SMALL(D3:X3,4),SMALL(D3:X3,5))

It seems and easy answer but I am no where near it
Any ideas?
 
Try this:

=IF(SMALL(D3:X3,1)=0,SUM(SMALL(D3:X3,2),SMALL(D3:X3,3),SMALL(D3:X3,4),SMALL(D3:X3,5),SMALL(D3:X3,6)),SUM(SMALL(D3:X3,1),SMALL(D3:X3,2),SMALL(D3:X3,3),SMALL(D3:X3,4),SMALL(D3:X3,5)))

This formula checks to see if the lowest number in your array is a
zero. If it is then the formula you were using before is altered
slightly by starting with the 2nd lowest number and adding from
there. Otherwise your original formula is used.
 
That didn't work because the answer was still zero
In the 21 cells from D3:X3 selected 10 have values and the rest are awaiting
data.
From the 21 cells I want to ignore the 11 zeros and calculate the 5 smallest
of the 10 other values.
 
One way would be to use a helper column with this formula copied down.......

=IF(A1=0,"",A1)

Then use your SMALL formula on that column


Vaya con Dios,
Chuck, CABGx3
 
@Homeonthecouch said:
I have a set of cells D3:X3, Some contain data, some as yet Don't
I want to add the lowest 5 numbers in these cells but do not want to
count the zero's

=SUM((SMALL(D3:X3,1),SMALL(D3:X3,2),SMALL(D3:X3,3),
SMALL(D3:X3,4),SMALL(D3:X3,5))
Click to expand...

So the cells that don't contain data contain 0 instead? If so, and if
the 'real' data is all positive, try

=SUM(SMALL(D3:X3,COUNTIF(D3:X3,0)+{1;2;3;4;5}))

If 'real' data could be positive and negative, it could also be 0 (by
continuity), in which case using 0 as a placeholder for missing values
would be unwise.
 
Now that worked a treat !
Many thanks

I will be back as I can see a another problem but it's way too late to be
opening that can of worms :

Once again many thanks to all that lent a hand

Thank You

Andrew
 
Back
Top