1. Welcome to the new Styleforum!

    We hope you’re as excited as we are to hang out in the new place. There are more new features that we’ll announce in the near future, but for now we hope you’ll enjoy the new site.

    We are currently fine-tuning the forum for your browsing pleasure, so bear with any lingering dust as we work to make Styleforum even more awesome than it was.

    Oh, and don’t forget to head over to the Styleforum Journal, because we’re giving away two pairs of Carmina shoes to celebrate our move!

    Please address any questions about using the new forum to support@styleforum.net

    Cheers,

    The Styleforum Team

    Dismiss Notice

The Excel Questions Thread

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

  1. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    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 Well-Known Member

    Messages:
    15,831
    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 Well-Known Member

    Messages:
    9,455
    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 Well-Known Member

    Messages:
    15,831
    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 Well-Known Member

    Messages:
    3,933
    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 Well-Known Member

    Messages:
    3,933
    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 Well-Known Member

    Messages:
    15,831
    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 Well-Known Member

    Messages:
    4,113
    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 Well-Known Member

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

    Duh. [​IMG]
     
  10. gdl203

    gdl203 Well-Known Member

    Messages:
    36,643
    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 Well-Known Member

    Messages:
    4,113
    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 Well-Known Member

    Messages:
    15,831
    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 Well-Known Member

    Messages:
    3,933
    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 Well-Known Member

    Messages:
    4,113
    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 Well-Known Member

    Messages:
    3,933
    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.
     
  16. dirknibleck

    dirknibleck Member

    Messages:
    8
    Joined:
    Apr 15, 2009
    The easiest way to dedupe a list is to use Data->Filter->advanced filter

    Select the copy to option. Indicate where you want the resulting deduped list to appear, and then check the unique values box.
     
  17. Connemara

    Connemara Well-Known Member

    Messages:
    39,486
    Joined:
    Mar 9, 2006
    I should probably learn how to use Excel. I can add and shit, that's about it. [​IMG]
     
  18. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    This is my new favorite threak /nerd.
     
  19. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    The easiest way to dedupe a list is to use Data->Filter->advanced filter

    Select the copy to option. Indicate where you want the resulting deduped list to appear, and then check the unique values box.


    The point of all this, I think, is to use formulas for everything. If you do all this filtering and copy/paste, your filtered list becomes obsolete if the original changes. Also, the programming behind the built-in functions in excel is very slick; by that I mean they execute much faster than anything you can come up with in VBA.
     
  20. Dewey

    Dewey Well-Known Member

    Messages:
    3,485
    Joined:
    Jul 19, 2007
    How do I create a chart that automatically updates itself?

    Let's say I have two columns of information -- date and, I don't know, money spent on shoes. And then I want to chart a 30-day average for money spent on shoes. Every day I update the money-spent-on-shoes column as I change the zeros to $money$$. Is there a way to make the line chart to update itself every day? It should grow horizontally day after day.
     

Share This Page

Styleforum is proudly sponsored by