Anyone have any cool excel functions/formulas that they don’t think too many other people know of?
I feel like I’ve hit a plateau and would love to pick up some new tips and tricks.
If anyone has any that they’d like to share (out of the basics) I’d appreciate it!
| +34 | latest on Shorenstein? | 9 | 5h |
| +20 | Probability of S2 recap returning equity | 9 | 1d |
| +20 | To the veterans: What was institutional dev actually like during the last bull run? | 8 | 2d |
| +16 | Development vs. REPE pay gap | 8 | 2d |
| +15 | PGIM Real Estate Equity Opinions? | 10 | 9h |
| +14 | How do I break into a family office role | 3 | 16h |
| +12 | MBA Pipelines for REPE | 10 | 11h |
| +12 | Reneging - What should I do? | 5 | 3d |
| +12 | Opportunistic Equity Acquisition Associate Role - Middle Market REPE | 1 | 14h |
| +11 | PRE MBA IB back to IB | 5 | 3d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
=mid is cool... saves me a bunch of time when someone puts some dumb formatting or something in rent rolls.
Basically =mid will list the text in a cell, skipping a certain number of characters and outputting a certain number of characters.
so if some rent roll has units listed as 0000049 0000050 etc, you can =mid(5,2) and change the units to 49 and 50
mid(),left(), and right() makes formatting things a lot easier. Also a trick a lot of people don't think about is that you can find and replace spaces with nothing. If it's one of those oddly formatted spaces, you can just copy it into the replace field. Was pretty crucial dealing with the various garbage the older Yardi reports spit out (maybe still do).
^^^
=value should fix that problem
others that are fun
unique
filter
textafter
textbefore
sum
sumifs
Trim, choose, offset
Use it wisely, in spreadsheets already struggling, it does add a bit of calculating depth to it, but I use =indirect a surprising amount. Two main use cases:
1. Combining data from multiple tabs. Recently needed to pull year X NOI from 30+ property tabs, I set the header to the tab name and was able to just copy/paste the formula. I know there are other ways to handle this too, but this is the easiest for me.
2. Scenarios, specifically for argus runs. I don't use argus anymore, but I used indirect to refer to one of 3 tabs named for different scenarios with argus cash flows, then a drop down to select which one.
Already mentioned above, but =Filter is a huge honorable mention.
more people should use =sumproduct to roll up monthly CFs into annual CFs
How do you do that? I always use sumifs based on row title and year.
I’ll try to explain below but kinda hard to do in writing.
It’s simple and similar to SUMIFS. I don’t like complex formulas or models but this is very easy to copy around without editing and allows for a more dynamic model IMO
=SP( (entire column of line items from your monthly cash flow tab = line item you want to return on acf tab)x(entire row of hold years from monthly cash flow tab = year you want to return in Acf tab) x entire monthly cash flow field)
Then lock columns, rows, etc as needed
There’s a YouTube video out there somewhere as I understand that may not be super clear
=LAMBDA for custom functions without using macros
Expedita maiores omnis accusamus ut sunt numquam rerum. In et sequi dicta quia et. Sed omnis dolores nisi nihil.
Dolores ipsa quae vitae ducimus. Rerum aut reiciendis odit non ut nisi beatae. In itaque ea quo ducimus cupiditate.
Adipisci dolorem quasi consequatur ut dolorem non qui. Ut tempore consequatur et corporis.
Voluptate suscipit molestiae quia voluptates facilis. Est consequatur ut repellendus explicabo. Repudiandae soluta delectus quia.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...