"Small"

M

M.A.Tyler

=SMALL(S1:S5,3)
This gives me a #NUM! error, is there a way to avoid this? It has a negitive
effect on subsequent calculations.

Thanks!

M.A.Tyler
 
T

T. Valko

What do want instead of the error?

This will leave the cell blank:

=IF(ISERROR(SMALL(S1:S5,3)),"",SMALL(S1:S5,3))

This will return a 0:

=IF(ISERROR(SMALL(S1:S5,3)),0,SMALL(S1:S5,3))

Biff
 
M

MartinW

Hi M.A.

The #NUM! error is most likely due to to text
entries in S1:S5

Copy a blank cell, select S1:S5, Edit>Paste Special>Add
and OK out.

HTH
Martin
 
P

Peo Sjoblom

Don't know who you are answering but num errors are not due to text, value
errors are


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)
 
M

MartinW

Yes Peo. but in the given example =SMALL(S1:S5,3)
if 1 or 2 of those entries are text it will return a false answer
and if more than 2 of those entries are text it will return a #num error
as there is not enough data to produce the third smallest.

Regards
Martin
 
B

Bob Phillips

It is if there are not 3 numbers in the range, so text or blank will
generate that error.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dave Peterson

Count the numbers first?

=if(count(s1:s5)<3,"not enough numbers",small(s1:s5,3))
 
Top