"Small"
 

=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

"Small"
 

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.A.Tyler" <Great Lakes State wrote in message
...
=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

"Small"
 

Can you tell us what you have in S1:S5?

"M.A.Tyler" wrote:

=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

"Small"
 

Hi M.A.

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

Copy a blank cell, select S1:S5, EditPaste SpecialAdd
and OK out.

HTH
Martin

"Small"
 

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)


"MartinW" wrote in message
...
Hi M.A.

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

Copy a blank cell, select S1:S5, EditPaste SpecialAdd
and OK out.

HTH
Martin

"Small"
 

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

"Small"
 

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)

"Peo Sjoblom" wrote in message
...
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)


"MartinW" wrote in message
...
Hi M.A.

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

Copy a blank cell, select S1:S5, EditPaste SpecialAdd
and OK out.

HTH
Martin

"Small"
 

This is the real trouble, is there a way around it?

"Bob Phillips" wrote:

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)

"Peo Sjoblom" wrote in message
...
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)


"MartinW" wrote in message
...
Hi M.A.

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

Copy a blank cell, select S1:S5, EditPaste SpecialAdd
and OK out.

HTH
Martin

"Small"
 

Count the numbers first?

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

M.A.Tyler wrote:

This is the real trouble, is there a way around it?

"Bob Phillips" wrote:

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)

"Peo Sjoblom" wrote in message
...
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)


"MartinW" wrote in message
...
Hi M.A.

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

Copy a blank cell, select S1:S5, EditPaste SpecialAdd
and OK out.

HTH
Martin







--

Dave Peterson