+ Reply to Thread
Results 1 to 2 of 2

Optimising a Cut Off

  1. #1
    Registered User
    Join Date
    03-25-2019
    Location
    Bournemouth, England
    MS-Off Ver
    Microsoft Office 365, Business
    Posts
    1

    Optimising a Cut Off

    Hi all, I have some data of the following format (this is a dummy version):

    ID type score
    id1 A -195
    id2 A 387
    id3 A -59
    id4 B 219
    id5 A 481
    id6 A 44
    id7 A 147
    id8 A 346
    id9 B -27
    id10 A 184
    id11 A 258
    id12 A 130
    id13 A 73
    id14 A 436
    id15 A -111
    id16 B 152
    id17 A 398
    id18 A 262
    id19 A 196
    id20 B 263
    id21 B -84
    id22 B 212
    id23 A -227
    id24 A 180
    id25 A 453
    id26 A 6
    id27 A 214
    id28 B -60
    id29 A 90
    id30 B -187
    id31 A -178
    id32 A 190
    id33 A 457
    id34 A -207
    id35 B 475


    I'd like to be able to set a threshold filter for the score, that maximises the number of 'B' ids removed and minimises the number of 'A' ids removed. Is this a possibility within Excel?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Optimising a Cut Off

    If you sort by score, you can calculate how many of each type are removed for any given threshold:

    A
    B
    C
    D
    E
    F
    G
    H
    1
    26
    9
    E1: =COUNTIF($B$2:$B$37, E2)
    2
    ID
    Type
    Score
    A
    B
    3
    id23 A
    -227
    -25
    -9
    E3: =COUNTIF($B$2:$B3, E$2) - E$1
    4
    id34 A
    -207
    -24
    -9
    5
    id1 A
    -195
    -23
    -9
    6
    id30 B
    -187
    -23
    -8
    7
    id31 A
    -178
    -22
    -8
    8
    id15 A
    -111
    -21
    -8
    9
    id21 B
    -84
    -21
    -7
    10
    id28 B
    -60
    -21
    -6
    11
    id3 A
    -59
    -20
    -6
    12
    id9 B
    -27
    -20
    -5
    13
    id26 A
    6
    -19
    -5
    14
    id6 A
    44
    -18
    -5
    15
    id13 A
    73
    -17
    -5
    16
    id29 A
    90
    -16
    -5
    17
    id12 A
    130
    -15
    -5
    18
    id7 A
    147
    -14
    -5
    19
    id16 B
    152
    -14
    -4
    20
    id24 A
    180
    -13
    -4
    21
    id10 A
    184
    -12
    -4
    22
    id32 A
    190
    -11
    -4
    23
    id19 A
    196
    -10
    -4
    24
    id22 B
    212
    -10
    -3
    25
    id27 A
    214
    -9
    -3
    26
    id4 B
    219
    -9
    -2
    27
    id11 A
    258
    -8
    -2
    28
    id18 A
    262
    -7
    -2
    29
    id20 B
    263
    -7
    -1
    30
    id8 A
    346
    -6
    -1
    31
    id2 A
    387
    -5
    -1
    32
    id17 A
    398
    -4
    -1
    33
    id14 A
    436
    -3
    -1
    34
    id25 A
    453
    -2
    -1
    35
    id33 A
    457
    -1
    -1
    36
    id35 B
    475
    -1
    0
    37
    id5 A
    481
    0
    0


    At 180, for example, you cut 13 A's and 4 B's. I don't understand your figure of merit, so I don't know what you do with that.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] nested if optimising
    By george.m in forum Excel General
    Replies: 4
    Last Post: 01-30-2018, 03:55 AM
  2. optimising formulas
    By Trebor777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-06-2017, 08:54 AM
  3. Help optimising my code
    By DanielPodo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-31-2017, 10:33 AM
  4. Optimising code
    By mpower87 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-31-2011, 11:49 PM
  5. Optimising with Formula
    By andrewc in forum Excel General
    Replies: 0
    Last Post: 06-17-2010, 07:46 AM
  6. Optimising row selection
    By dems in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2010, 03:50 AM
  7. optimising this VBA
    By brave.inf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2009, 09:44 AM

Tags for this Thread

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