Quantcast

Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Apr 10, 2013
Messages
9,127
Location
Squat Rack
People who love INDEX MATCH over VLOOKUP, please explain when/how it is useful. I am having a helluva time working it into my excel routine.
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.

And yes, it still works across sheets/books. It was just easier for me to demonstrate in one sheet.
 
Joined
Dec 21, 2013
Messages
9,154
Location
LFK
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.
 
Joined
Apr 10, 2013
Messages
9,127
Location
Squat Rack
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.
Wordsmith, this guy.
 
Joined
Dec 21, 2013
Messages
9,154
Location
LFK
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.
 
Joined
Apr 10, 2013
Messages
9,127
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.
 
Joined
Dec 21, 2013
Messages
9,154
Location
LFK
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.
I would convert everything to seconds, sum, and then convert back.

Not pretty or efficient but simple enough for me.
 
Joined
Apr 10, 2013
Messages
9,127
Location
Squat Rack
I would convert everything to seconds, sum, and then convert back.

Not pretty or efficient but simple enough for me.
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!
 
Joined
Apr 5, 2013
Messages
3,924
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.
 
Joined
Jan 30, 2014
Messages
13,888
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.
 
Joined
Apr 10, 2013
Messages
9,127
Location
Squat Rack
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.
Copypasta data from 2003 into 2010? Redo chart?
 
Joined
Jan 30, 2014
Messages
13,888
Location
Chicagoish
Copypasta data from 2003 into 2010? Redo chart?
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.
 
Joined
Apr 10, 2013
Messages
9,127
Location
Squat Rack
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.
Fuck the man. Make your own chart styles.
 

Arbitrator

Not Patrick
Joined
Apr 10, 2013
Messages
12,090
I use R on and off. Not really at all an expert yet. I'm really a Stata guy at heart, but I would very much like to be competent in R so that I can eventually all my analysis in a piece of software I can have at home for free rather than having to rely on the software my employer pays thousands of dollars a year for...
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?
 

Latest posts

Top