+ Reply to Thread
Results 1 to 5 of 5

Problem with SMALL function

  1. #1
    Registered User
    Join Date
    02-09-2005
    Posts
    4

    Problem with SMALL function

    Hi,

    I'm getting a #NUM! error when using the SMALL function. I have entered =SMALL(V2:z2,1) which is correct. The problem appears to occur because cells V2:Z2 contain formulas, not pure numbers.

    If I test the function on different cells that contain only numbers, it works fine, but it won't work on the cells I need it for. The cells contain formulas which produce an integer (1,2,3,4,5 or 9) so I don't see why it should cause a problem, but it just won't work.

    I have tried the official help and it doesn't mention any restrictions in its use. Can anyone else help me out?

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Which formula are you using to produce those integers?

    Quote Originally Posted by JazzMan
    Hi,

    I'm getting a #NUM! error when using the SMALL function. I have entered =SMALL(V2:z2,1) which is correct. The problem appears to occur because cells V2:Z2 contain formulas, not pure numbers.

    If I test the function on different cells that contain only numbers, it works fine, but it won't work on the cells I need it for. The cells contain formulas which produce an integer (1,2,3,4,5 or 9) so I don't see why it should cause a problem, but it just won't work.

    I have tried the official help and it doesn't mention any restrictions in its use. Can anyone else help me out?

  3. #3
    Registered User
    Join Date
    02-09-2005
    Posts
    4
    Quote Originally Posted by Domenic
    Which formula are you using to produce those integers?
    A combination of IF and AND:

    =IF(AND(T2<0.051,T2>-0.051)=TRUE,"1","9")

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by JazzMan
    A combination of IF and AND:

    =IF(AND(T2<0.051,T2>-0.051)=TRUE,"1","9")
    Because the numbers are within quotes, the value being returned is text, not numerical. Therefore, remove the quotes from your formula...

    =IF(AND(T2<0.051,T2>-0.051),1,9)

    Hope this helps!

  5. #5
    Registered User
    Join Date
    02-09-2005
    Posts
    4
    Brilliant! Thanks a lot. The silly Function Wizard put the quotes in for me

    Quote Originally Posted by Domenic
    Because the numbers are within quotes, the value being returned is text, not numerical. Therefore, remove the quotes from your formula...

    =IF(AND(T2<0.051,T2>-0.051),1,9)

    Hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1