EX101x: Week 1 and 2 recap

I haven’t been as consistent with these recaps as I would like to be, but as I get closer to my first exam in 5 years, I’ve been cooling it on the MOOC front. As a result, I’m ending up completing two weeks in one most of the time now.

Week 1 focussed on using conditional functions like IF, COUNTIF, SUMIF etc. Week 2 was about using lookup and search functions like VLOOKUP, INDEX and MATCH. A few things which I did for the first time:

  1. Import data into a sheet from a web feed: I’ve never had a need to use this function, and I’m not sure if I have the need now, but it’s good to know.
  2. Using a time criteria for COUNTIF: So useful, and I can’t believe that in my 15 years of Excel usage, I have never needed to do something like this.
  3. A use case for setting the range_lookup for VLOOKUP as TRUE: I use VLOOKUP a lot (or used to, before I switched to doing most of my analysis in Python). I also use a lot of nested IF statements for ranges of items, and some combination of INDEX/MATCH.

    At school, we were always taught “FALSE with VLOOKUP” because every scenario always required exact matches. I never though to question that, nor did I have the need to, because with all the GIS data that needs pre-processing, we always require exact matches.

    Now that I know that by creating a lookup table and using TRUE, VLOOKUP can be used to match value ranges the same way I would use a nested IF statement, I can see how that would really have helped me out on a few projects a while ago.

A big part of why I also focussed on learning more Python wherever I could was because I was tired of exiting ArcGIS, manipulating data in Excel, then bringing it back into ArcGIS. With Python, I could do all the data manipulation I wanted right there, and then take it into Excel for non-GIS people.

I’m also really pleased that I can still learn things in Excel to increase my productivity. One becomes so accustomed to doing things a certain way, that if that method is fast enough, one tends not to look for a replacement.

Advertisements

One thought on “EX101x: Week 1 and 2 recap

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s