In case you don’t like sports or somehow manage to interact with zero people who like sports (or maybe you live in a place where American football isn’t so popular), something pretty interesting happened this past Sunday. The worldwide leader had a “not top 10” moment of its own that I bet they won’t be replaying for the entertainment of its millions of viewers. [Read more…]
You know what the worst thing is that can happen to a consultant trying to get promoted?
You telling them exactly what they need to do to lock in that bump to the next level.
Here’s why. [Read more…]
You’d be surprised how simple the calculations are that sophisticated investors rely on. Payback period is one such example. [Read more…]
In case you missed, the set up for this video, you can read some additional information about payback period here. I literally just had to pull something together using this, so this isn’t just random Excel functionality.
If you’re interested in downloading the Excel workbook you can get that by clicking here.
Let me know what you think of the video in the comments.
This weekend, I read the follow-up to one of the most bizarre stories in recent memory. The story involved a young Ivy League student, by the name of Nayla Kidd, who went completely off the grid for about three weeks.
Nayla’s story caught my eye originally because we went to the same small boarding school, and after she was reported missing, there were a number of people sharing posts looking for any details on her whereabouts. Fearing the worst, I also shared her story in hopes that her family would have some closure one way or another.
Nayla recently resurfaced and this is where things start getting a little weird. [Read more…]
Please Log in to view this content.
Do you know about the Excel wildcard functionality?
You know, the one where you put an “*” before and/or after something you’re searching for and Excel executes a search based on what you input. For example, imagine you have a spreadsheet with a lot of data. Maybe it’s an export of transaction activity for the month. You have hundreds of account numbers ranging from 1xxx through 9xxx. You want to spend a little extra time on the 8xxx accounts because that’s where the bulk of activity gets posted. You can do one of two things:
Option 1: Add a filter, go to the filter drop down and unselect all accounts, then scroll down to the accounts you want to look at and manually check them.
Option 2: Add a filter, go to the search in the filter drop down and type in “8*”. Not only is option 2 faster (Under option 1, you could be scrolling for days through a bunch of accounts that show up because of one row of data in your file.), but it’s also more reliable. Using a manual approach, you could easily miss a box that you meant to check and skew your analysis.
Pretty sweet, right? And it gets better.
You can also use Excel wildcard functionality in formulas, and I’m going to show you how with another example. Say you have this small table of data:
Note: “X” represents contractor and “E” represents employee in the grade column and the number represents level with the respective contractor or employee category. Now, let’s say you want to use formulas to summarize a few different pieces of information:
1. Salary paid to employees and contractors, respectively
2. Salary paid to employees and contractors with the last name “Brown”
3. Salary paid to employees whose names contain “Brown” in them.
How would you attack this problem? Looking at number 1 above, you might be tempted to just add a column to flag each employee as a flag and then use a sumif formula, but that strategy quickly becomes cumbersome and impractical as the number of extra columns needed in your dataset increases.
This is where the Excel wildcard function comes in handy. Here’s how to take down each of the above:
1. For salary paid to contractors, type =SUMIFS(E4:E11,D4:D11,”*X*”). For employees, do the same thing but replace the X with an E in the formula.
2. For salary paid to contractors with the last name Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown”). Again, for employees, do the same thing but replace the X with an E in the formula.
3. For salary paid to contracts with the last name containing Brown, type =SUMIFS(E4:E11,D4:D11,”*X*”,C4:C11,”*Brown*”).
You notice the slight difference between 2 and 3? Trust me when I tell you adding this functionality to your repertoire will save you a lot of time. There is one other formula I used to put this example together.
This time last year I was distraught about what to do next in my career.
I had just pinged my HR contact to figure out when the business cases needed to be submitted for people wanting to get promoted to director.
When he told me the deadline was a week ago, it was like he punched me with every ounce of force had in his body.
Panic. Embarrassment. Frustration. Anger at the system. Anger at myself. I was feeling all of it at the same time, to the point of physical paralysis. [Read more…]
Part of what makes Excel so great is the way you can create great visualizations of data. It isn’t all about performing complex calculations and tracking action items. [Read more…]