Discarding zero's

  • Thread starter @Homeonthecouch
  • 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?
 
M

Mark

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.
 
@

@Homeonthecouch

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.
 
C

CLR

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
 
H

Harlan Grove

@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.
 
@

@Homeonthecouch

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
 
Top