The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Let us free up the next 5 hours of your life. Need to stay sharp now that I'm in back in school.

    Edit: Changed the original title, since we're all in this together.
     


  2. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.
     


  3. gnatty8

    gnatty8 Senior member

    Messages:
    9,478
    Likes Received:
    1,449
    Joined:
    Nov 12, 2006
    Location:
    Not in Atlanta, GA
    Please create a stochastic interest-rate economic pricing model for your average single-premium deferred annuity.

    I have one of those for bond options, could probably be finagled to do what you want.. You will need a copy of Crystal Ball or be able to program a monte carlo engine in VBA for it to work..
     


  4. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    I have one of those for bond options, could probably be finagled to do what you want.. You will need a copy of Crystal Ball or be able to program a monte carlo engine in VBA for it to work..

    Haha, no I already have the model. I was just being a douchebag.
     


  5. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Haha, no I already have the model. I was just being a douchebag.

    Good because for a second I thought this was going to be too easy.
     


  6. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    But seriously I was thinking more generic, along the lines of "how do I consolidate a vertical list of items that are at separated randomly by blank cells? i.e.

    from:

    a

    b
    c


    d

    e

    f

    to:

    a
    b
    c
    d
    e
    f

    ?"
     


  7. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    But seriously I was thinking more generic, along the lines of "how do I consolidate a vertical list of items that are at separated randomly by blank cells? i.e.
    "


    Is it doable without a macro? I always had to write a macro to do that.
     


  8. dcg

    dcg Senior member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    Is it doable without a macro? I always had to write a macro to do that.

    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.
     


  9. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.

    Duh. [​IMG]
     


  10. gdl203

    gdl203 Affiliate Vendor Dubiously Honored Affiliate Vendor

    Messages:
    36,834
    Likes Received:
    17,375
    Joined:
    Jun 9, 2005
    Location:
    New York
    Don't overthink it. Highlight the column, Data -> Sort -> Ascending.

    I thnk you took the a, b, c, d part a little too literally. Just a guess
     


  11. dcg

    dcg Senior member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    I thnk you took the a, b, c, d part a little too literally. Just a guess

    Should work regardless of the data, no? All we're trying to do is get rid of the blanks.
     


  12. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    Even if you needed it ordered in a specific way, you could just add ordered numbering in an adjacent column and re-order to the old order after the sort.
     


  13. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    I do it with an array formula (assume the data is in column A1 -> Ax), in cell B1:

    {=INDIRECT("A"&SMALL(IF($A$1:$A$x<>"",ROW($A$1:$A$x)),ROW(A1)))}

    Drag down.
     


  14. dcg

    dcg Senior member

    Messages:
    4,113
    Likes Received:
    501
    Joined:
    Nov 2, 2007
    Location:
    Philly
    Here's a question for the OP (or anyone else that might feel like answering).

    Say you have a list of a few thousand cells that you need deduped. Ex:

    1
    1
    2
    5
    10
    13
    13
    13
    16
    22
    ....

    Assume your list starts in A2. When I've had to accomplish this I've entered

    =IF(A2=A1, 1, 0)

    into B2, then copied -> paste special -> values both columns, then sorted both columns by column B ascending, giving you a deduped list in column A for all rows where column B is 0.

    Is there an easier way to dedupe a list?
     


  15. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Here's a question for the OP (or anyone else that might feel like answering).

    Say you have a list of a few thousand cells that you need deduped. Ex:

    1
    1
    2
    5
    10
    13
    13
    13
    16
    22
    ....

    Assume your list starts in A2. When I've had to accomplish this I've entered

    =IF(A2=A1, 1, 0)

    into B2, then copied -> paste special -> values both columns, then sorted both columns by column B ascending, giving you a deduped list in column A for all rows where column B is 0.

    Is there an easier way to dedupe a list?


    I would modify the first formula I posted:

    {=INDIRECT("A"&SMALL(IF($A$2:$A$15<>$A$1:$A$14,ROW($A$2:$A$15)),ROW(A1)))}

    Drag down.
     


Share This Page

Styleforum is proudly sponsored by