In this post I’m going to show one of my favourite financial modeling tricks: how to use Aggregate to Count Visible Rows. Background Often, when I’m building models in Excel, I like to group key assumptions at the top of
I’m working on a spreadsheet where users will be able (required) to insert new rows at a later date. When they do so, it’s critical that the section subtotals always… well… subtotal correctly. The challenge, of course, is that you
Something that can be very handy when you’re building a dashboard is to return a certain picture depending on a condition. We can use VLOOKUP to look up data in a table and return the corresponding value from a different
The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for
The following article is courtesy of Yoav Ezer, who volunteered to do a guest post for me while I am at the summit. Thanks Yoav! User Defined Functions versus Formulas in Excel Which are better, Excel functions or UDFs? Use
Today I realized that one of my GETPIVOTDATA formulas was returning an error, since there wasn't any result in the Pivot Table it was checking for data. Unfortunately, during the design of the spreadsheet I never wrapped the function with
I need to thank Joe Chirilov and Danny Khen, from the Excel team at Microsoft for this tip. We were discussing some Excel things while we were having dinner during the summit, and I was bemoaning the fact that it
The last couple of times I've taught, I've received a question about how to deal with the following scenario: You have a table of data, say in A1:A10 In A11, you have the following formula:Â =sum(A1:A10) Someone highlights row A11,
Introduction How many times have you built a table of data, and put totals on it. You ship it out the door, and another user comes along and inserts a new row right before the totals row? If you have
There are three types of spreadsheets built in the world today: The "quick 'n' dirty for your eyes only" type spreadsheet. It doesn't need to be pretty, and doesn't need a ton of time spent making it readable or understandable
Copyright © 2022-present Excelguru Consulting Inc. All rights reserved.
