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 Modicom of MOD - When to use this divisive function and what to be careful of... Function
AGGREGATE - Most complex function ever..? Functions
Allocating Weeks to Months - How to allocate weekly calculations to the correct reporting month. 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.
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.
Chart Labels - How to move the chart labels in mysterious ways. Charts
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
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.
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
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
Forecasting Tips - Trendy methods in Excel. Applied Excel
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
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
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
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
Multiple Criteria - How to deal with summarising data based upon multiple criteria. Lookups
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
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
Pivotal PivotTables - How to use PivotTables to turn data into information. Data
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.
Report at the Double - How to use macros to copy report pages to a new document without creating links to confidential data.
Rolling Budgets - How to maintain rolling budgets / forecasts in Excel. Modelling
Self Reviews - The checks in this post.
Simulation Stimulation - How to undertake simple simulations analysis using nothing more than Excel and some of its lesser known functions and functionalities. 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.
Sumproduct Squared..? - Well, Sumproduct by SumProduct must be Sumproduct squared, yes..?
Tables - Summary of the new feature introduced in Excel 2007. Data
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
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
Variable (Dynamic) Hyperlinks - How to create a hyperlink that can link to multiple destinations depending upon conditions being fulfilled. Applied Excel
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
Working Capital Adjustments - Credit where credit's due: how to model debtors and creditors efficiently.

Register

Thought

SumProduct Thought

Contact Us Today