Excel Tips & Tricks ITT

Discussion in 'Totally Off-Topic' started by Fingolfin, Oct 27, 2014.

Help Support TalkBeer Community by donating:

  1. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Everything Zach said. It is much easier to select the column for return value rather than count the columns from an array in VLOOKUP.

    If columns D, E, F are my data source and I want to return email addresses to a list of unique identifiers, this is the syntax.
    [​IMG]
    And yes, it still works across sheets/books. It was just easier for me to demonstrate in one sheet.
     
    jtmartino and tehzachatak like this.
  2. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    See it's the comparing across spreadsheet tabs I am having trouble executing.
     
  3. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Shouldn't matter. You just need the tab identifier (Sheet2!)

    I'll screen cap an example of that, too...
     
    tehzachatak likes this.
  4. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Here's the source data in its own tab (Sheet2):
    [​IMG]
    And the table with updated syntax (this is in Sheet3)
    [​IMG]
     
    chaggy5, Rempo and tehzachatak like this.
  5. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    Will look at it again when I can study it good. I think I keep inserting the pieces in the wrong order.
     
    Fingolfin likes this.
  6. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    71,618
    Location:
    FIB
    VLOOKUP goes:

    VLOOKUP([Find this value],[...in the first column of this array], [...and if you do find it give me corresponding data from this column to its right], [FALSE because close never counts])

    INDEX MATCH goes:

    INDEX([Give me data from the same row of this column],MATCH([...where you find this value],[...in this column],0))

    More or less you do it backwards. In VLOOKUP you specify what you're looking for first and then say where to find it and then which column to return. In INDEX MATCH you specify what you want returned and then say which value to find where.
     
    jtmartino, Rempo, Canti4341 and 2 others like this.
  7. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Wordsmith, this guy.
     
    quirkzoo likes this.
  8. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    Well your example helped. I got a 0 instead of NA.
    Your example doesn't appear to be in any kind of order, so that's not it.
     
  9. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    71,618
    Location:
    FIB
    Whoever designed charting in Excel is a fuck. The simplest shit imaginable is an adventure every goddamn time.

    I want a simple clustered bar chart. I highlight my data. A chart appears. Oh no, the clustered items are not appearing
    "together." It's as if each item is it's own category. No bother - I'll just reformat the data a bit. Oh great. Now it recognizes these items as connected, but it won't push them together so they are touching. Excellent. I can either have every item touching or none of them. Better attempt again to reformat. Didn't work? Every option I need is greyed out still?

    *Google*
    *Reads a bunch of shit that was valid in 2007 or 2010, but they helpfully removed it for us in 2013*
    *Finds nothing on 2013's layout*

    *Excel help*
    Oh good a very helpful explanation of what a "chart" is. Neat.
    *Nothing on how specific buttons work, when options become available, or what stupidly specific format your data must be in to activate various options*

    At this point, I could just draw the damn thing by hand.
     
    Arbitrator, jtmartino, Espeer and 6 others like this.
  10. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    ???

    I have time to assist today as needed.
     
  11. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    I am on vacation now. No Excel help for at least ten days.
     
  12. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Anyone have tips for summing time? Format of cells is HH:MM:SS. The values are not time of day, but rather total time spent. So some cells have hundreds of hours.
     
  13. RedCoffee

    RedCoffee

    Joined:
    Dec 21, 2013
    Likes Received:
    71,618
    Location:
    FIB
    I would convert everything to seconds, sum, and then convert back.

    Not pretty or efficient but simple enough for me.
     
    Fingolfin likes this.
  14. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    So I ended up converting the cells to "general" instead of "time". This gave me values as decimals of days. I then multiplied by 24 to give me hours. Good enough. Thanks for the seconds conversion idea, tho!
     
    RedCoffee likes this.
  15. axeman9182

    axeman9182

    Joined:
    Apr 5, 2013
    Likes Received:
    22,572
    Just saw this thread, lots of good stuff as I spend a lot of my day working in spreadsheets. At work we use a lot of vlookups with a match function for the argument where you would specify how many columns to go out. It's pretty handy because you just need to know the column header for the value you want, not how far out it is, and if the file format changes, as long as the column header doesn't change then your formula still works.
     
    Fingolfin and trickytunadicky like this.
  16. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    Forgive me if I asked this before. Maybe we have new people in here, and this is driving me batty.

    Anyone know how to swap horizontal axes in Excel 2010? It's really easy in 2003 and they either took it away or buried it in the Misty Mountains of Ribbon Land. My chart was created in 2003 and was since converted to 2010. When I go to expand the dates shown, suddenly those dates are only shown on the secondary horizontal axis. I can boot in XP and swap axes in 2003. It's a complicated metric chart that I would like to be able to fix in 2010 if possible.
     
    Fingolfin likes this.
  17. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Copypasta data from 2003 into 2010? Redo chart?
     
  18. trickytunadicky

    trickytunadicky

    Joined:
    Jan 30, 2014
    Likes Received:
    87,078
    Location:
    Chicagoish
    I would first have to convince someone upstairs they want these charts to look like 2013 charts, then get all of my compatriots to finish migrating to Windows 7. They all have to look the same. Dem Office 2010 charts have fancier colors and nice rounded edges.
     
  19. Fingolfin

    Fingolfin

    Joined:
    Apr 10, 2013
    Likes Received:
    98,369
    Location:
    Squat Rack
    Fuck the man. Make your own chart styles.
     
  20. Arbitrator

    Arbitrator Not Patrick

    Joined:
    Apr 10, 2013
    Likes Received:
    126,825
    Sorry to threadjack, but I would love any help from you or any R experts.

    Problem #1
    I'm using base R (not RStudio) with the knitr package to write a report. Current workflow is:

    - Write in R markdown (.Rmd)
    - knit() to markdown (.md)
    - markdownToHTML() to a web page
    - Print webpage to .PDF

    I want to stress that this is the stupidest workflow imaginable. However, the pandoc program can't seem to interface with R readily, so I can't use render() to convert .Rmd to .PDF. Is there a step-by-step "for dummies" version that I can follow? The pandoc webpage is useless.

    Problem #2
    I'm required to output the report as (report / discussion first) + (appendix containing code and figures).

    Knitr evaluates code in order. So if I reference anything inline, I am effectively calling a variable that R doesn't know, and it shits itself and stops the knitting.

    I'm putting important evaluation code at the beginning, with the suppressing "include=FALSE" option. But I'd like to show the exact same code and its output again in the Appendix so that someone can follow the logic. The problem is that it re-runs the code so I get different statistical values. Is there a way to 'freeze' the result of an evaluation aside from storing it in a variable and calling that variable in the output?
     
    Fingolfin likes this.