Was wondering what formulas most of you are using on the job? Thanks.
I have excel tests that I will have to do for some final round interviews so was wondering if there are any recommended websites to practice.
Popular Content See all
- Ray Dalio: Capitalism Needs to Be ReformedBridgewater's Ray Dalio [wrote a post about "Why and How Capitalism Needs to be Reformed,"](https://www.linkedin.com/pulse/why-how-capitalism-needs-reformed-parts-1-2-ray-dalio/?published=t/) claiming that the outcomes of capitalism are no longer consistent with the system’s...
- What do you do if you have a thing for your directorAssociate in my mid-late twenties, my director is amazing - I just really like her. Maybe I'm telling you all because I can't tell anyone else.
- JP Morgan Ending Campus VisitsIn a Bloomberg article last week, JP Morgan announced that it will begin using online behavioral science-based games to recruit college students. [quote]"Beginning this year, the bank will be using the games, instead of in person campus recruiting and traditional college campus visits,...
- AMA: Investment Banking Associate ***4/9 UPDATE: This is taking a little more time than I expected, and while I do appreciate and welcome all the questions, I will be a little less active on this starting tomorrow. Will still try to get to all questions though. Feel free to ask, comment and discuss though. Thanks all....
- IB in Los Angeles - banks, groups, culture?Hoping to learn more about investment banks in Los Angeles. My current firm's only california presence is in San Francisco. I've done a bit of research and think I understand which banks are in LA, but it's hard to tell what groups are out there. Also hoping to know a little more...
- AMA: I'm a Dry Bulk Physical TraderI'm a Physical Trader in the Asia region and look after a combined book of +1Mt for origination and sales of dry-bulk commodities. My background has been in some way or form of international trade, with 6+ years in operations before getting into a trading gig. I trade majority of dry bulk...
- Keeping Management Motivated Post-ExitHey all, I was able to acquire a small managed service business a few months ago ~1M Revenue, 300k EBITDA almost all recurring revenue. I am looking to acquire another one in a different area ~3h flight away from where I am living now. The deal is ~2M Revenue and ~500K EBITDA, with about...
- A job requires me to tell a great knock knock joke to be considered.... help me out hereSomeone plz give me a good knock knock joke that is funny but not corny. I need to make a good impression and I SUCK at knock knock jokes
- Leaving a single manager to start a book at a platformHas anyone made the switch from being an analyst at a single manager to being a PM on any of the platforms? As background, I've been at a single manager for 6 years and have covered my sector for a total of 8 years. I'm making ok but not great money for someone my level (500-750K)...
- Niche Real Estate ProductsOk, fellas. I'm bored to tears in real estate and am looking for new interesting paths and new challenges. What are some niche real estate products that people rarely think about? Self-storage was the trendy niche that, in my view, is now overbought in a lot of U.S. markets. What are some...
Leaderboard See all
| 1 |  | 97.4 |
| 2 | | 97.2 |
| 3 | | 97.2 |
| 4 | | 96.9 |
| 5 | | 96.8 |
| 6 | | 96.7 |
| 7 | | 96.7 |
| 8 | | 96.7 |
| 9 | | 96.6 |
| 10 | | 96.4 |
Upcoming EventsSee all
- Apr12
- Apr12
- Apr15
- Apr15
- Apr16
Comments (91)
(new value/old value) - 1 for calculating growth rates lul
Just a tip - parentheses are not needed due to order of operations, which is the whole advantage of writing it this way instead of (new - old) / old.
Index(match,match) all day, every day, twice a day.
Can you explain this function and what you use it for?
Finding specific values in a table where you don't necessarily know which column row the item is in. Similar to vlookup but a bit beefed up.
As an aside, I definitely got dinged at an interview for not knowing this. Then I entered the workforce and literally never encountered a situation where I needed it until ~2 years in.
Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.
If you used vlookup instead of index match in my organisation you would probably be fired.
Index match is also more dynamic. If you add columns into a table later, or change the order of columns the formula will not break.
Sum
Magical Vlookup& IF and Pivot tables
Pornhub.com
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
Offset
Count
If(And
If(And(Offset(Count
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Can you explain when/why you'd used "If(And(Offset(Count" ? creating a dynamic waterfall scenario?
It's actually not properly sequenced.
I use If(And(Count(Offset a lot to be able to change debt structures, reversion period, and to adjust TI/LCs for downtime.
Using count just eliminates the need to number your periods at the top of the pro-forma by counting the dates instead and offset lets you toggle different scenarios listed on the summary tab.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Countif
Sumproduct (and be able to use conditional sumproduct)
seconding this
other than that, either succumb to the firm preference with LOOKUPS or become exceedingly good at writing a vlookup/hlookup on the fly
It was mind-opening when I realized 90% of reports (at the firm) were done automatically feeding through other software. I feel bad for the guys I know at Morgan Stanley who have to write reports from excel all day every day.
Another man of culture I see... sumproduct is an incredibly versatile formula when dealing with large amounts of arrays
edate and eomonth
*(insert condition here)
As it saves me writing an if statement since I'm lazy
sumif...a lot
hlookup/hlookup....a lot
offset, pretty handy
date/eomonth...a lot
hlookup and count and match together, makes an array dynamic for when you add in rows or take out rows and still calculate from the same row.
floating arrays for data are pretty cool too.
Crazy nested if formulas...a lot
Lol, this thread brings me back to the Analyst days of REFing the shit out of a formula and fucking up the entire model then frantically trying to fix it before someone notices.
Haha yep, this is why iferror has become one of my favorite formulas. If you error check everything properly (especially circular references), it becomes pretty hard to key in a letter somewhere by accident and blow up the entire analysis.
Howeva!!...
IFERROR, if used on everything in the world, can slow a sheet and book down, especially if what's inside the () is cross-sheet referencing. I try to limit my IFERROR to things I know have the possibility to error out.
Market Share curves based on 5 inputs? Yes
Sum of segmented revenue streams? Nah
Might be a dumb question, but how do you get super proficient in utilizing these excel formulas? I'm currently in UG and have the REFM Modeling course. Am currently reading Linneman's textbook and taking some RE Finance and Investment Analysis at UCLA Extension. I also have my Argus Cert, but when reading this thread I feel like I am lacking in Excel.
Practicing/using it everyday
sorry if stupid question, ive seen people mention this but ive never had this issue since im always saving multiple versions of a file im working on....e.g. v1, v2, v3 i dont work directly with debt or anything but i sit next to our Liquidity team and they never have such issues due to making multiple versions each time they make a change or put something through. why does this seem to be different in banking / re?
Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.
Everyone here has pretty much covered the most common and useful formulas so I will weigh in on a couple resources for those trying to refine their Excel skills:
If you are entrepreneurial and self disciplined however the first two will give you a great basis for an analyst career.
SUMIF
INDEX(Match(Match constantly
SUMPRODUCT for cash flow roll-ups
OFFSET
IF/Then and SUMPRODUCT
There is currently a battle in my company over the use of the SUBTOTAL command. It's the nerdiest thing ever and it cracks me up.
Team subtotal.
You seem to be a gentleman and a scholar, so I think I know where you stand.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Seconded on SUBTOTAL
when modelling from scratch sumproduct is life.
Don't need much more.
Maybe some sum ifs and minima.
I've been using indirect a lot lately. Has been an incredibly versatile tool in organizing a lot of crazy data
SUMIF
SUMPRODUCT
IF then Nested formulas
Date functions
INDEX MATCH
If I'm pulling in cash flows for multiple months then always index match so I can just drag the formula across without changing column numbers. I'll use VLookup if I'm just doing a simple look up for one value.
Carl+Z
large, small can be helpful for me too
other than that, mostly index match, sumif, sumproduct
F2
=IFERROR(VLOOKUP(HOES,ANYWHERE,FALSE),"GET FADED")
I agree with most everything on here but I gotta be honest, if you think index is superior to v/hlookups in all instances you're doing it wrong.
I'm curious, when are the lookups better than index?
2 advantages of VLOOKUP beyond the obvious simpler syntax:
IMM is of course a better tool to use for standardized, repetitive models, but often it's too complex for a small lookup and search function.
I use VLOOKUP much more often, but like IMM for the heavy lifting modeling stuff we do.
Also, there are easy ways to get around the 'fixed' column/row reference and you can make that dynamic pretty easily, so if someone else goes in and deletes/adds rows to the table it won't cause an error.
For waterfalls, being proficient with MAX(MIN ()) and MIN(MAX()) is helpful
Something said this but Sumproduct(-- = incredible for summary charts
F1
Datedif
Excel is primitive. Step your game up folks and get on some real data analytics software :D
How many of you Excel guru's can operate without a mouse? I've heard you become light years quicker once you learn all the keyboard shortcuts and hotkeys.
Trying to decide if I want to put in the brain damage to learn or just keep using mouse.
I can use the keyboard for almost all functions. But there are some things that are just too annoying to do without a mouse like some formatting.
What formatting is quicker with a mouse?
Shortcuts probably save more time formatting than they do w/ anything else.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
It's pretty simple - just start slamming that alt button.
The vast majority of my work time is in Excel, and I'd say the time saved working through keyboard shortcuts rather than a mouse amounts to a 3-4 hours a week. Definitely worth learning.
Besides obvious ones like INDEX(MATCH), I use these ones a ton:
-INDIRECT
-SUM(OFFSET())
-MID()/LEN()
-CONCATENATE()
-ISNUMBER()
concatenate is too long; just use ampersands in between your concatenation values
Alt H + B + H
This bottom borders be thicc
can you give a few more like this...super helpful
Some quick shortcuts I use all the time:
Ctrl + Shift + & = puts boarder around selected area
Ctrl + Shift + - = Erases all boarders in a selected area
Ctrl + Shift + $ = Aromatically puts numbers in Currency format (Although most like to use Accounting format without "$" sign, so Ctrl + 1 = brings up format window and just use Tab key to get to accounting and put in "none" for currency symbol and 0 decimals)
Ctrl + [ = shows exactly where cells are in a selected formula by highlighting them. I like it better than tracing dependents/precedents
Alt + H + 0/9 = increase or decrease decimals. I for some reason find myself using this a lot
Good comments above. Some of my favorites:
I like commenting when I have long formulas using +N("COMMENT").
Also, recording macros and editing them a bit if you find yourself repeating something across many tabs.
I'm just an intern, so probably what I use is different. But I use a lot of vlookups, sumifs, if combined with and/or. I know that these aren't formulas, but a lot of work with the shortcut keys.
IF() - mixed with AND()/OR()
EDATE()
SUMIF()
INDEX/MATCH
SUMPRODUCT
DATEDIF - (MOD(DATEDIF)) - handy for quarterly/bi-annual payments
IFERROR - careful with this one!
DATE()-YEAR()-MONTH()
NORMDIST() - for my s-curves
MAX/MIN
PMT() - for investment loan calcs
VLOOKUPS for finding information between spreadsheets and pulling out results based on emails/outreach
HLOOKUPS for the exact same thing, just in different formats
Conditional Formatting isn't a function, but the visualization it gives you is amazing when presenting data to coworkers.
Offset(match...match)
Named ranges
Pivots
If/If(And
Sumproduct
Conditional formatting
Vlookups and Hlookups pale in comparison to Offset(Match..Match)
Alt -> H -> O -> I
Alt - E - S - T
Alt - E - S - V
Alt - H - O - R
Cool formulas but you all are missing a crucial one:
Dsum
Want to Vote on this Content?! No WSO Credits?
Join Us
Already a member? Login
Related Content
See allPopular Content
See all