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. haganah

    haganah Well-Known Member

    Messages:
    6,351
    Joined:
    Nov 24, 2007
    Location:
    New York, NY
    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.
    http://spreadsheetpage.com/index.php...nter_new_data/
     
  2. gnatty8

    gnatty8 Well-Known Member

    Messages:
    9,455
    Joined:
    Nov 12, 2006
    Location:
    Not in Atlanta, GA
    Haha, no I already have the model. I was just being a douchebag.

    douchebag..
     
  3. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    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.


    There's an easy way... and a macro way. The easy way is just to keep the empty rows hidden. The chart will condense itself to only show the unhidden days. You will have to unhide the rows to do your updates, and then rehide the same rows (minus the new one that is no longer empty), and your chart will also update.

    If you're okay with VBA it's not a huge chore. The reason you have to use VBA to do this seamlessly is that the formulas the dictate the ranges of a data series in a chart don't accept functions (EDIT: But they DO accept named ranges!!!).

    For instance, the following is not valid:

    =SERIES(,Sheet1!$A$2:$A$23,INDIRECT("Sheet1!$B$2:$B$23"),1)

    ...even though I think it should be. Maybe Excel 2007 is different, but I haven't used it.
     
  4. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
  5. AntiHero84

    AntiHero84 Well-Known Member

    Messages:
    2,425
    Joined:
    Aug 28, 2008
    Location:
    Long Island
    How can I fit an oversized excel table to a 1-page word document?
     
  6. teddieriley

    teddieriley Well-Known Member

    Messages:
    8,488
    Joined:
    Apr 8, 2006
    Location:
    Wait, you smell that?
    Nerds. I know how to make charts.
     
  7. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    How can I fit an oversized excel table to a 1-page word document?

    Do you want to embed it or are you okay with just having the image? Excel to word/ppt is a pain and I'm no good at it.
     
  8. gdl203

    gdl203 Well-Known Member

    Messages:
    36,643
    Joined:
    Jun 9, 2005
    Location:
    New York
    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.

    I don't know if you were being facetious but how would adding a, b and c on the side be faster than removing the rows? [​IMG]

    That indirect fn does the trick without the additional labor/time - which I assume was the question to begin with
     
  9. dirknibleck

    dirknibleck Member

    Messages:
    8
    Joined:
    Apr 15, 2009
    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.

    You're right, but if the requirement is only to produce the unique list once, an array formula is overkill, and generally beyond the comprehension of everyday users. I find that frequently people forget that there are simple ways to do things in Excel, and instead use more complicated features - ie. Macros, and sometimes Array formulas, because they have used these tools in the past to manage a more difficult problem.
     
  10. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    I don't know if you were being facetious but how would adding a, b and c on the side be faster than removing the rows? [​IMG] That indirect fn does the trick without the additional labor/time - which I assume was the question to begin with
    The indirect is much better than my proposed solution. What I meant was type 1 on the top row in the adjoining column. Drag down to the end of the column, which gives you ordinal numbering of all entries. Sort by name. Delete blanks at bottom. Sort by numbers. The end. Yeah, it sucks, but it's a lot faster than manually deleting.
     
  11. JoelF

    JoelF Well-Known Member

    Messages:
    1,459
    Joined:
    Dec 31, 2007
    Location:
    MA & NYC
    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?


    In Excel 2007 it's just Data=>Eliminate Duplicates, I assume you're using a previous version where that does not exist.
     
  12. Dewey

    Dewey Well-Known Member

    Messages:
    3,485
    Joined:
    Jul 19, 2007
    Interesting answers. Thanks for the excel help.
     
  13. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    There was this, back in August. A fun distraction. Jinda made a better version but it's long gone now.
     
  14. Kent Wang

    Kent Wang Well-Known Member

    Messages:
    5,720
    Joined:
    May 5, 2005
    Location:
    London
    I need this in Google Spreadsheet, which has a similar but smaller set of functions.

    I have a grid of numbers. I want to sum only the negative numbers. The way I do this now is create an extra column and set all the values to the formula =IF(A1<0, A1,), then I sum that column. Can I do this without that extra column?
     
  15. MetroStyles

    MetroStyles Well-Known Member

    Messages:
    15,831
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    I need this in Google Spreadsheet, which has a similar but smaller set of functions.

    I have a grid of numbers. I want to sum only the negative numbers. The way I do this now is create an extra column and set all the values to the formula =IF(A1<0, A1,), then I sum that column. Can I do this without that extra column?


    sumif(A1:A10, "<0")
     
  16. whacked

    whacked Well-Known Member

    Messages:
    7,364
    Joined:
    Sep 24, 2006
    This is my new favorite threak /nerd.

    +1. I learn some(or a bunch of)thing new everyday.


    Is there a good (read: well-organized, preferably free) book/website where I can learn about macros and Excel functions from the ground up? TIA,
     
  17. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
    +1. I learn some(or a bunch of)thing new everyday.


    Is there a good (read: well-organized, preferably free) book/website where I can learn about macros and Excel functions from the ground up? TIA,


    J Walkenbach's books are really good. There's one for formulas and one for VBA (Excel 2003: Power Programming with VBA). Not free, but very easy to follow and goes from the ground up.
     
  18. Dewey

    Dewey Well-Known Member

    Messages:
    3,485
    Joined:
    Jul 19, 2007
    The kitonbrioni wardrobe generator is classic. Thanks for re-posting that.

    ===============

    New problem

    Say I have 25 rows of numbers, ranging from 0 to 9, across 40 columns. How do I average the 30 highest numbers in each row?

    I have solved this but it requires a huge sheet and some manual editing at the end.
     
  19. CunningSmeagol

    CunningSmeagol Well-Known Member

    Messages:
    3,933
    Joined:
    Oct 15, 2006
    Location:
    New York
  20. Kent Wang

    Kent Wang Well-Known Member

    Messages:
    5,720
    Joined:
    May 5, 2005
    Location:
    London
    sumif(A1:A10, "<0")
    Wow, I feel dumb for not knowing about this function.

    But now I realize I also need to ignore where column B = "Transfer". I've figured out how to do each, e.g. ignore where B=Transfer and ignore where C<0, but don't know how to combine them.
     

Share This Page

Styleforum is proudly sponsored by