+ Reply to Thread
Results 1 to 4 of 4

Knock-off the largest and smallest numbers with opposite sign conventions

Hybrid View

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    7

    Knock-off the largest and smallest numbers with opposite sign conventions

    Given a random set of numbers,

    1. select and knock-off the numbers with the highest and lowest absolute values OR the highest or lowest distance from zero.
    2. BUT these two numbers should have different sign conventions. i.e. one should be positive and other negative.
    3. Zeros "0" are of no consequence as we are knocking off balances.
    4. The other numbers are not to be disturbed.

    #1
    -5 is the smallest, 20 is the largest

    A A1 A2
    -5 10 20
    0 10 15

    #2
    10 is the smallest, -50 is the largest

    A A1 A2
    -50 10 20
    -40 0 20

    #3
    20 is the smallest, -100 is the largest
    A A1 A2
    -100 -10 20
    -80 -10 0

    #4
    no knocking off requried
    A A1 A2
    -100 -10 -20
    -100 -10 -20

    #5
    no knocking off requried
    A A1 A2
    100 10 20
    100 10 20

    #6
    the zero value is ignored
    A A1 A2
    100 0 -20
    80 0 0

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,047

    Re: Knock-off the largest and smallest numbers with opposite sign conventions

    Why is in {-50 10 20} 10 smallest, and in {-100 -10 20} 20 is smallest (and not 10)?
    Never use Merged Cells in Excel

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Knock-off the largest and smallest numbers with opposite sign conventions

    in {-50 10 20} 10 is smallest as it is closest to zero value.

    in {-100 -10 20} 20 is smallest as - 10 and -100 have the same sign convention. but i see that therein lies another issue, with the existing logic, no knocking-off will take place as the algo will pick -10 as smallest and not 20. Good point.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,047

    Re: Knock-off the largest and smallest numbers with opposite sign conventions

    No need for duplicated posts. Since this is with example workbook, this one can be closed.

+ 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