need to find smallest 3 values in table with #DIV/0!

[freak11]

hi guys,
i am trying to find the smallest three values in a column but this column has #DIV/0! and whatever formula i type in i get #DIV/0!
. min() and small() dont work. can anyone help?

[Richard Buttrey]

Hi,

If you only have Excel 2003 then wrap your formula in an =IF(ISERROR()) formula. i.e

=IF(ISERROR(your_formula),"",yourformula)

[FDibbins]

#DIV/0!
means just that - you are trying to divide something by 0. So you need to structure that formula to catch that.

If B2 is blank of 0, then A1/B1 will result in #DIV/0!
but youi could error trap with...
=if(iserror(A1/B1),"",A1/B1)
or
=IF(OR(B1=0,B1=""),"",A1/B1)

[martindwilson]

depends if you have no 0 values than you could use this array formula filled down

=SMALL(IF(ISERROR(1/$A$1:$A$20),10^99,$A$1:$A$20),ROWS($A$1:A1))

[Tony Valko]

Try this...

Data Range

	A	B	C
1	48		10
2	98		33
3	#DIV/0!		48
4	52
5	87
6	33
7	10
8	#DIV/0!
9	96
10	91	------	------

This array formula** entered in C1 and copied down:

=IF(COUNT(A$1:A$10)>=3,SMALL(IF(ISNUMBER(A$1:A$10),A$1:A$10),ROWS(C$1:C1)),"")

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

[vlady]

how about aggregate? Don't know if will work in '03
something like

=AGGREGATE(15,6,$A$1:$A$10,ROW(A1))

[Tony Valko]

how about aggregate? Don't know if will work in '03

AGGREGATE was introduced in Excel 2010.

[martindwilson]

it wont ,wasn't introduced until 2010

[vlady]

thanks both. that's what i'm afraid of.
if just the OP could upgrade he can benefit from the new functions introduced by excel.

[martindwilson]

£199.99 for the sake of ctrl+shift+enter?

[Tony Valko]

£199.99 for the sake of ctrl+shift+enter?

If the OP really is using Excel 2003 the price to upgrade is worth it just for the IFERROR function alone!

[martindwilson]

i only ugraded from 97 to 2007 because i got it free!