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


A Debt to Repay04.08.2011How to model debt repayment calculations transparently (without using Add-In functions).
A Modicom of MOD15.08.2011When to use this divisive function and what to be careful of...
AGGREGATE04.05.2014Most complex function ever..?
Allocating Weeks to Months05.04.2015How to allocate weekly calculations to the correct reporting month.
Array of Light04.04.2010Providing an overview of array formulae in Excel.
Asking for a Date03.08.2011Dealing with dates for files used with both Excel 2003 and Excel 2007.
Auto Fill Becoming a Drag..?04.05.2013Had your fill of Auto Fill? Maybe this will rekindle your interest...
Automated File Names05.08.2011How to get the file name to appear in a spreadsheet automatically.
Automating a Table of Contents06.07.2012A simple navigation routine.
Avoiding a Group Protection Racket11.02.2014How to use grouping (outlining) on protected worksheets.
Being Direct About INDIRECT05.07.2012Beating around the bush with Excel.
Broken Charts29.03.2015How to create "breaks" in line charts when the chart data has been calculated rather than input.
Concatenation03.08.2011Can you see the join?
Conditional Formatting04.04.2010How to change the appearance of a cell; comparing the new Excel 2007 features with earlier incarnations.
Consistent Formulae04.08.2011How to seek out inconsistencies consistently.
Copying Formulae Across Rows29.03.2015Copying formulae across a wide range without getting Repetitive Strain Injury.
Creating a Timestamp05.08.2011Letting your date leave a mark on your spreadsheet.
Data Tables05.08.2011How to use one of Excel's built-in features to undertake sensitivity analysis in seconds.
Data Tables with Off-Sheet Inputs01.01.2014How Data Tables can reference inputs on other worksheets.
Data Validation04.07.2012Exercising control in your spreadsheets.
DATEDIF02.11.2013The proverbial black sheep of the Excel family?
Depreciation Appreciation19.03.2010How do you model the total depreciation charge for a given period?
Desperately Seeking Solver27.07.2013Discussing the pro's and con's of Goal Seek, Solver and VBA approaches to generating desired outcomes.
Evaluating EVALUATE21.11.2012How to use an obscure function that doesn't seem to want to work...
File Style03.04.2010How to make the most of Excel's styles, in contrast to simple cell formats.
Filter Tips with No Health Warning05.07.2012Considering the pros and cons of both the AutoFilter and the Advanced Filter.
Finding an EXACT Match05.07.2014Locating your perfect match.
Finding the Nth Item on a List05.07.2014How to locate later occurrences of an item on a list.
Forecasting Tips04.08.2011Trendy methods in Excel.
Goals Gone Wild26.07.2014Still haven’t found what you’re looking for? It happens to the best of us and probably U2. Maybe Excel’s wildcards can help.
Hiding Formulae05.08.2011How to hide and protect formulae used in a spreadsheet.
Hyperlinking Chart Sheets04.04.2010You can't hyperlink to and from chart sheets in Excel. Or can you..?
INDEX MATCH03.04.2010Consideration of one of the most powerful combinations of functions for Excel modelling.
Interest Received28.03.2010How to calculate interest received on the average cash balance for the period.
Irreverent IRR03.08.2011Understanding some of the issues associated with the Internal Rate of Return (IRR) in Excel.
Keyboard Shortcuts03.04.2010One of the most comprehensive lists of Excel shortcuts available on the Internet.
Linking to PowerPoint03.08.2011Presenting Excel in a new light.
Locating Links03.04.2010How to locate and remove unintended links in spreadsheets.
Locating Links #205.08.2011More on this popular topic: how to generate a list of links in an Excel file using a transferable macro.
Managing Scenario Manager13.12.2012How to generate a simple scenario analysis using Excel’s built-in Scenario Manager tool.
Many Happy Returns: Modified Internal Rate of Return (MIRR)03.07.2012Doing exactly what it says on the tin.
Model Construction Tips04.04.2010Advice on the do's and don't's of putting a financial model together.
Modelling Actual versus Budget15.08.2011How to keep track of actual data versus the original budgeted / forecast information.
Modified Internal Rate of Return Revisited02.11.2013Considering how to calculate "XMIRR": evaluating the return for aperiodic intervals.
Multiple Criteria04.04.2010How to deal with summarising data based upon multiple criteria.
Multiple Criteria with OR27.02.2013How to sum data based on satsfying at least one of several criteria
Multiple Multiple Criteria02.12.2012How do you sum numbers based on multiple criteria regading data contained in multiple rows within multiple worksheets..?
Multiple Number Formatting01.01.2014How to circumvent the supposed Excel limit of number formats in one cell.
Names15.08.2011Highlighting the pros and cons of using names in Excel.
Number Formatting04.04.2010Demystifies the #@?*ing syntax associated with number formatting.
Number of Days in the Month05.07.2012How to work out how many times a particular day of the week occurs in a certain month.
Onset of OFFSET03.04.2010Explaining how useful the displacement function OFFSET can be for modellers.
Pivotal PivotTables04.04.2010How to use PivotTables to turn data into information.
Protect / Unprotect Multiple Worksheets03.10.2012Wearing protection: how to get Excel to actually do what you want.
Reducing File Size16.07.2013Common methods to reduce the size of an Excel file.
Referencing Every Nth Row07.04.2015Presenting two simple approaches to create an output that refers to cells N rows apart.
Report at the Double19.04.2014How to use macros to copy report pages to a new document without creating links to confidential data.
Rolling Budgets29.03.2015How to maintain rolling budgets / forecasts in Excel.
Self Reviews05.10.2012The checks in this post.
Simulation Stimulation14.12.2012How to undertake simple simulations analysis using nothing more than Excel and some of its lesser known functions and functionalities.
SUBTOTAL - Function and Functionality19.04.2014How to turn data into information using a combination of the SUBTOTAL function and Subtotal functionality.
Sum Every Nth Item05.04.2015Presenting a simple formulaic approach for summing every Nth item in a list.
Sumproduct Squared..?27.07.2012Well, Sumproduct by SumProduct must be Sumproduct squared, yes..?
Tables05.08.2011Summary of the new feature introduced in Excel 2007.
Text Messages06.07.2014You may not be a physicist but it’s time for some (text) string theory, even if it text all day to explain.
Time Series05.07.2012No time series problems - period!
Tornado Charts05.08.2011A step-by-step guide on how to construct one of the most commonly used sensitivity charts.
Transpose, I Suppose05.05.2013Considering common methods of switching data between rows and columns (or vice versa).
Variable (Dynamic) Hyperlinks05.04.2015How to create a hyperlink that can link to multiple destinations depending upon conditions being fulfilled.
Volatile Functions: Talk Dirty to Me05.04.2015Considering how volatile functions and actions may be slowing down your models unnecessarily.
Waterfall Charts19.04.2010How to create charts that depict key movements period on period.
Working Capital Adjustments03.10.2012Credit where credit's due: how to model debtors and creditors efficiently.