Better ways to display data?

Discussion in 'Mac Apps and Mac App Store' started by AdamA9, Apr 22, 2010.

  1. AdamA9 macrumors 6502a

    Joined:
    Feb 2, 2010
    #1
    hey guys,

    I couldn't think of a good place to put this, so I thought here is as good as any.

    I need some help. On a monthly basis I take a download of data, and currently am just pasting this into Excel, and all my calculations run and graphs update and everything - which is great because it means I don't have to do a lot of work when presenting this. However, I've noticed as the data is growing, the calculations are taking longer and longer to run, and now it's got to the point of crashing.

    Is there something better than Excel, that I can use to just paste my data in, and then have it produce tables and graphs showing the data as I need it?

    Thanks Guys.

    :)
     
  2. miles01110 macrumors Core

    miles01110

    Joined:
    Jul 24, 2006
    Location:
    The Ivory Tower (I'm not coming down)
    #2
    If you want a GUI interface then no, Excel is the best you're going to get. If you are willing to learn a pseudo programming language, look into R or root.
     
  3. yellow Moderator emeritus

    yellow

    Joined:
    Oct 21, 2003
    Location:
    Portland, OR
  4. AdamA9 thread starter macrumors 6502a

    Joined:
    Feb 2, 2010
    #4
    OKay thanks, I was hoping it would be a simple move to something that could just pick up my data, query it and display it all nice for me.
     
  5. Corrosive vinyl macrumors 6502

    Corrosive vinyl

    Joined:
    Sep 22, 2006
    #5
    yes, I have come to realize, like as stated above, Excel is the best app. It is absurd the amount of stuff that it can do. maybe updating your mac would help the matter, like upping the RAM.
     
  6. AdamA9 thread starter macrumors 6502a

    Joined:
    Feb 2, 2010
    #6
    I'm actually doing this on a Windows PC. I'm at work you see, with just 2GB RAM. There are quite a lot of calculations running on these sheets, with various look-ups.
     
  7. andiwm2003 macrumors 601

    andiwm2003

    Joined:
    Mar 29, 2004
    Location:
    Boston, MA
    #7
    if you are willing to go into windows then spotfire decision suite is the way to go. very expensive, takes some training but is by far the best way to analyse and present large multivariate data sets.
     
  8. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #8
    One other approach is to have running updates. That is, you import the data into a massive database (worksheet in Excel), then set up other worksheets which drive the data, and then other worksheets for the display and still others for the graphs. I set up such a system with direct download from our mainframe. It used to take them 10-15 hours per week. By rearranging and automating, then entire process took 25 minutes, and 15 was downloading from the mainframe. Most of the other 10 minutes were updating the ppt displays that were automatically linked to the Excel displays. (All done using XL2003 on Windows XP)

    You might want to check out Excel User. Charley Kyd is one of the best at training in this area; see especially Dashboards.

    Worth a look anyway.
     
  9. AdamA9 thread starter macrumors 6502a

    Joined:
    Feb 2, 2010
    #9
    You know that actually makes sense, I don't know why I didn't think of that! Thanks :)
     
  10. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #10
    Well, I'm sure it sounds like your set up. The part I didn't cover was the running totals (each worksheet can have its own length of time, etc.), so that you can set up how much is displayed (how many days, weeks, months, etc.) by changing only one cell in the entire workbook.

    Thus, a workbook that originally was 90 MB and took an average of 25 minutes of recalculation time, was reduced to just under 10 MB, with an average 30 seconds recalculation time. It is not the amount of data, but how you access that data. Some functions are very processor intensive and bog the workbook down; i.e. they recalculate every time you make any changing in the workbook. By changing setup and types of functions and formulas, you can significantly reduce the time it takes.

    But, hey, just offering another thought, from an old codger who has been there and done it.
     
  11. AdamA9 thread starter macrumors 6502a

    Joined:
    Feb 2, 2010
    #11

    It sounds like the exact set up I need. So thanks for your thoughts, it's a great help if it reduces my stress when running this sheet. The problem I was having was that every time I made an adjustment a calculation was run, and that was slowing me down. I thought about turning off auto-calculation, but I'm going to give your suggestion a go next week. I'm running rows and rows of calculations such as:

    =SUMPRODUCT(--(ISNUMBER(SEARCH(D24,Data!AT:AT))),--(Data!K:K="IT/Software - Europe/E-L/iPA/Workflow / AEP"),--(Data!E:E>=$V$9),--(Data!E:E<=$W$9))

    and

    =SUM((Data!AM:AM="iPA Workbench error")*(Data!K:K="IT/Software - Europe/E-L/EDMS/Unlock Batch"))

    And I don't know if these COUNTIF and SUMPRODUCT calculations are excessively resource hungry though? :confused:
     
  12. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #12
    You might want to check out this as well: Decision Models discussion of volatile functions, especially the fine distinction involving SUMIF examples.

    SUMPRODUCT is very powerful but carries a large overhead in terms of calculation. By rearranging data setup in the data worksheet, you can use simpler functions and many more and at the same time drastically reduce calculation times.

    BTW both of your examples have the same overhead.
     

Share This Page