SumProduct Thought

The Thought section is a list of topics suggested by visitors to the site. If you would like to suggest a future topic please drop us a line at contact@sumproduct.com

A Debt to Repay - How to model debt repayment calculations transparently, without using Add-In functions... MODELLING
A Decided Lack of Style - How to get rid of styles when their numbers get out of control... MODELLING
A Modicum of MOD - When to use this divisive function and what to be careful of... Function
A to Z of Excel Functions - The compilation of all the Function blogs we have published so far!... EXCEL, Modelling, Formulas
AGGREGATE - Most complex function ever..? Functions
Aggregating Aggravating Time Periods - How to consolidate data by different periodicities... MODELLING
All Creatures MAX, MIN, LARGE and SMALL - When to use the MAX, MIN, LARGE and SMALL functions... MODELLING
Allocating Weeks to Months - How to allocate weekly calculations to the correct reporting month. Modelling
ALTernative Excel in an Instance - Looking at Excel instances, and what they are... EXCEL
Are You Absolutely Sure? - How to avoid common mistakes in cell referencing when modelling... MODELLING
Array of Light - Providing an overview of array formulae in Excel. Excel Tips
Asking for a Date - Dealing with dates for files used with both Excel 2003 and Excel 2007.
Auto Fill Becoming a Drag..? - Had your fill of Auto Fill? Maybe this will rekindle your interest... Excel Tips
Automated File Names - How to get the file name to appear in a spreadsheet automatically. Applied Excel
Automating a Table of Contents - A simple navigation routine.
Avoiding a Group Protection Racket - How to use grouping (outlining) on protected worksheets.
Avoiding Bad Hyperlinks - A simple way of avoiding a very common issue with hyperlinks... APPLIED EXCEL
Avoiding Macros: Solve for X - How to avoid using macros by using basic math instead. MODELLING
Being Direct About INDIRECT - Beating around the bush with Excel. Functions
Broken Charts - How to create "breaks" in line charts when the chart data has been calculated rather than input.
Calculating Interest Rates Correctly - How to correctly calculate interest rates... MODELLING
Creating Similar Charts Side by Side - How to create comparison charts with similarly-scaled axes... CHARTS
Chart Labels - How to move the chart labels in mysterious ways. Charts
Comparing Lists Simply - How to compare multiple lists without macros or complicated formulaic logic (well, sort of...). EXCEL TIPS
Concatenation - Can you see the join? Excel Tips
Conditional Formatting - How to change the appearance of a cell; comparing the new Excel 2007 features with earlier incarnations. Formatting
Consistent Formulae - How to seek out inconsistencies consistently. Auditing
Copying Formulae Across Rows - Copying formulae across a wide range without getting Repetitive Strain Injury. Applied Excel
Creating a Timestamp - Letting your date leave a mark on your spreadsheet. Applied Excel
Data Tables - How to use one of Excel's built-in features to undertake sensitivity analysis in seconds. Data
Data Tables with Off-Sheet Inputs - How Data Tables can reference inputs on other worksheets. Modelling
Data Validation - Exercising control in your spreadsheets. Applied Excel
DATEDIF - The proverbial black sheep of the Excel family? Functions
Debt Sculpting - How to get rid of common macros by organising their spreadsheets better... MODELLING
Depreciation Appreciation - How do you model the total depreciation charge for a given period? Modelling
Desperately Seeking Solver - Discussing the pro's and con's of Goal Seek, Solver and VBA approaches to generating desired outcomes.
Do You Choose to Use CHOOSE? - How to use the CHOOSE function in Excel... MODELLING
Dynamic Arrays: One Year On - A look back on dynamic array functions and features... EXCEL, FORMULAS
Dynamic Range Names - How to create dynamic range names... MODELLING
Entering the Import / Export Business - Tips for linking data between workbooks... MODELLING
Erring on the Side of #CAUTION! - How to understand and fix the various errors that can occur in Excel... MODELLING
Evaluating EVALUATE - How to use an obscure function that doesn't seem to want to work... Functions
Excel 2016 Functions and Features - Excel 2016 has released a half dozen new functions... FUNCTIONS
Excel 2019 Functions and Features - Excel 2019 has released new functions, charts, feature improvements, and more... EXCEL TIPS
Excel 365 Functions and Features: February 2019 - Excel 365 has just received several performance improvements.... EXCEL TIPS
File Style - How to make the most of Excel's styles, in contrast to simple cell formats. Formatting
Filter Tips with No Health Warning - Considering the pros and cons of both the AutoFilter and the Advanced Filter. Data
Finding an EXACT Match - Locating your perfect match.
Finding the Nth Item on a List - How to locate later occurrences of an item on a list. Lookups
First and Last Lookups - Have you ever needed to interpolate data between two points? MODELLING
Forecasting Tips - Trendy methods in Excel. Applied Excel
Formulae Referencing Current Worksheet - Looking at the possible errors that may occur when referencing other worksheets, and how to fix them... MODELLING
Getting a Date if You’re a Modeller... - Why it text so long to get a date in the modelling industry? MODELLING
Getting Arrays: Spilling the Beans on Seven New Functions - Understanding the seven new functions. FUNCTIONS
Go Poisson - This article looks at calculating the inverse of a Poisson distribution for scenario modelling in a a simple, pragmatic approach for non-academics. MODELLING, POISSON INVERSE DISTRIBUTION
Goals Gone Wild - Still haven’t found what you’re looking for? It happens to the best of us and probably U2. Maybe Excel’s wildcards can help. Data
Hiding Formulae - How to hide and protect formulae used in a spreadsheet. Excel Tips
Hiding Unhideable Objects - How to hide/group rows and/or columns when these insist on staying put. Excel Tips
Historical vs. Actual vs. Forecast - How to create formulea which considers historical, actual and forecast data simultaneously... MODELLING
Hyperlinking Chart Sheets - You can't hyperlink to and from chart sheets in Excel. Or can you..? Applied Excel
INDEX MATCH - Consideration of one of the most powerful combinations of functions for Excel modelling. Functions
INDEX MATCH INDEX - Has INDEX MATCH met its MATCH? FUNCTIONS
Interest Received - How to calculate interest received on the average cash balance for the period. Modelling
Irreverent IRR - Understanding some of the issues associated with the Internal Rate of Return (IRR) in Excel. Functions
Keyboard Shortcuts - One of the most comprehensive lists of Excel shortcuts available on the Internet. Charts
Linking to PowerPoint - Presenting Excel in a new light. Applied Excel
Locating Links - How to locate and remove unintended links in spreadsheets. Excel Tips
Locating Links #2 - More on this popular topic: how to generate a list of links in an Excel file using a transferable macro. Excel Tips
Looking Up to LOOKUP - Looking for a useful function to replace VLOOKUP? FUNCTIONS
Lookout for VLOOKUP and HLOOKUP - Why we all need to stop using the VLOOKUP function... FUNCTIONS
Managing Scenario Manager - How to generate a simple scenario analysis using Excel’s built-in Scenario Manager tool. Modelling
Many Happy Returns: Modified Internal Rate of Return (MIRR) - Doing exactly what it says on the tin.
Model Construction Tips - Advice on the do's and don't's of putting a financial model together.
Modelling Actual versus Budget - How to keep track of actual data versus the original budgeted / forecast information. Modelling
Modified Internal Rate of Return Revisited - Considering how to calculate "XMIRR": evaluating the return for aperiodic intervals. Functions
Modifying Numbers Inappropriately - How accountants can potentially cause major errors in their sums (and how to avoid it!). MODELLING
Multiple Criteria - How to deal with summarising data based upon multiple criteria. Lookups
Multiple Criteria with Errors - How to deal with errors in data when summing up subject to multiple criteria... MODELLING
Multiple Criteria with OR - How to sum data based on satsfying at least one of several criteria Lookups
Multiple Multiple Criteria - How do you sum numbers based on multiple criteria regading data contained in multiple rows within multiple worksheets..? Lookups
Multiple Number Formatting - How to circumvent the supposed Excel limit of number formats in one cell. Formatting
Names - Highlighting the pros and cons of using names in Excel. Applied Excel
New Data Types and FIELDVALUE Function Now Generally Available (Sort Of) - Understanding the new Data Types in Excel. EXCEL
Number Formatting - Demystifies the #@?*ing syntax associated with number formatting. Formatting
Number of Days in the Month - How to work out how many times a particular day of the week occurs in a certain month. Modelling
Onset of OFFSET - Explaining how useful the displacement function OFFSET can be for modellers. Functions
Order of Operations - Understanding the order of operations in Excel to avoid potential errors... MODELLING
Power BI Tips Blog Series - The compilation of all the Power Pivot blogs we have published so far!... Power BI
Pivotal PivotTables - How to use PivotTables to turn data into information. Data
Power Pivot Principles Blog Series - The compilation of all the Power Pivot blogs we have published so far!... EXCEL, Power Pivot,
Power Query Blog Series - A compilation of all the Power Query blogs we have published so far!... POWER QUERY
Protect / Unprotect Multiple Worksheets - Wearing protection: how to get Excel to actually do what you want. Applied Excel
Reducing File Size - Common methods to reduce the size of an Excel file. Excel Tips
Referencing Every Nth Row - Presenting two simple approaches to create an output that refers to cells N rows apart.
Referencing Multi-Page Data from a Web Site with a Single URL - How to link data on a website where it is on more than one page of a table, all with the same URL?... POWER QUERY
Report at the Double - How to use macros to copy report pages to a new document without creating links to confidential data.
Revising Forecasts - How to revise forecasts as timings move... MODELLING
Rolling Budgets - How to maintain rolling budgets / forecasts in Excel. Modelling
Rolling Charts - How to create an automatic rolling chart... MODELLING
Running in a Field - How to stop counters in tables running out of hand. EXCEL TIPS
Seasonal Forecasting - Tips on seasonal forecasting... MODELLING
Self Reviews - The checks in this post.
Simulation Stimulation - Undertake simple simulations analysis using nothing more than Excel and some lesser known functions & functionalities. Modelling
Slicer of Good Fortune - When to use slicers, and potential issues to look out for… MODELLING
Smoothing Capex - How to smooth capital expenditure, and why is it important... MODELLING 101
Sorting Sorted (Sort of!) - How to deal with sorting – both from a static and a dynamic perspective... MODELLING
Spotting Inconsistencies and Plugs - How to spot potential errors by going to a special shortcut... EXCEL TIPS
Spreadsheet Skills: Flagging IF - Everything you need to know about IF flags... MODELLING
SUBTOTAL - Function and Functionality - How to turn data into information using a combination of the SUBTOTAL function and Subtotal functionality. Functions
Sum Every Nth Item - Presenting a simple formulaic approach for summing every Nth item in a list.
SUM Things to Consider - Looking at SUM in ways you may not have considered previously... FUNCTIONS
Sumproduct Squared..? - Well, Sumproduct by SumProduct must be Sumproduct squared, yes..?
Tables - Summary of the new feature introduced in Excel 2007. Data
Taking Stock of Inventory - How to properly calculate movements in inventory and associated cost of of goods sold... MODELLING
Take it to the Limit - And I can't tell you why... EXCEL TIPS
Text Messages - You may not be a physicist but it’s time for some (text) string theory, even if it text all day to explain. Data
The Check’s in the Post - We Heard you like checks so we put a check in your checks... APPLIED EXCEL
Time Series - No time series problems - period! Modelling
Tornado Charts - A step-by-step guide on how to construct one of the most commonly used sensitivity charts. Charts
Transpose, I Suppose - Considering common methods of switching data between rows and columns (or vice versa). Excel Tips
Unique RANK - How to order numerical data when there are duplicates or blanks. Functions
Using OFFSET for Depreciation - How to use OFFSET to model depreciation... MODELLING
Using OFFSET for Scenario Analysis - How to use OFFSET with "what-if"analysis... MODELLING
Variable (Dynamic) Hyperlinks - How to create a hyperlink that can link to multiple destinations depending upon conditions being fulfilled. Applied Excel
VBA Blog Series - A compilation of all the VBA blogs we have published so far!... VBA, Visual Basic Advanced
Version Aversion - How to identify which version of Excel you have and update it. EXCEL TIPS
Volatile Functions: Talk Dirty to Me - Considering how volatile functions and actions may be slowing down your models unnecessarily. Functions
Waterfall Charts - How to create charts that depict key movements period on period. Charts
When Modelling Dozen Work - The quick and dirty dozen tips for checking over spreadsheets. EXCEL TIPS
Working Capital Adjustments - Credit where credit's due: how to model debtors and creditors efficiently. MODELLING
XLOOKUP and XMATCH: Two New X-Men for Excel - The newly added functions in late August 2019. EXCEL TIPS