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

Achieving the Impossible: Reversing Text - I would like to discuss a key issue frequently encountered in Excel... EXCEL, FUNCTIONS
A Debt to Repay - How to model debt repayment calculations transparently, without using Add-In functions... MODELLING
A Modicum of MOD - When to use this divisive function and what to be careful of... Function
A Decided Lack of Style - How to get rid of styles when their numbers get out of control... MODELLING
A to Z of DAX Functions - The compilation of all the DAX Function blogs we have published so far!... EXCEL, PowerPivot, Power BI
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 Fees - I shall be seeking to model forecast fees for the calendar year EXCEL, 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 Depreciation for Existing Capital Expenditure - How to calculate depreciation for existing capital expenditure... MODELLING
Calculating Interest Rates Correctly - How to correctly calculate interest rates... MODELLING
Calculating the Pre-Tax Cost of Equity - How to calculate the pre-tax cost of equity. MODELLING
Charts and Dashboards Blog Series - The compilation of all the Charts and Dashboards blogs we have published so far!... CHARTS, EXCEL TIPS
Chart Labels - How to move the chart labels in mysterious ways. Charts
Coming to Terms with Dates - I shall consider allocating days across monthly periods, where some dates are outside the forecast period and others straddle the (monthly) reporting periods. EXCEL
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 Custom Number Formatting - How to create custom number formatting... MODELLING
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 Calendar with Dynamic Arrays - How to create a calendar with dynamic arrays... EXCEL
Creating a Timestamp - Letting your date leave a mark on your spreadsheet. Applied Excel
Creating Similar Charts Side by Side - How to create comparison charts with similarly-scaled axes... CHARTS
Data Tables - How to use one of Excel's built-in features to undertake sensitivity analysis in seconds. Data
Data Tables Re-Revisited: Care with Inputs - How to show changes to a particulate input without Data Tables... DATA TABLES
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
Depending on Data Validation Lists - There are several ways that Excel may control what end users may input into a spreadsheet, but one and of the simplest and most intuitive is the use of data validation. EXCEL TIPS
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 Data Validation - How to search through long data validated lists using dynamic arrays. ..APPLIED EXCEL
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
Fee FIFO Fumbles - The easy way to model inventory, is to use a simple averaging method to value the stock sold. Unfortunately real-life kicks in more often than not EXCEL
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 – Manual versus Automatic? - Useful Modelling Functions #6... 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, and Dynamic Arrays... 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
How Much Do I Need - When business slackens, you need to control your cash before it controls you. Therefore, let me help you analyse your future cashflow requirements EXCEL
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
Keeping Up Appearances – Conditional Formatting - How to model debt repayment calculations transparently, without using Add-In functions. MODELLING
Keyboard Shortcuts - One of the most comprehensive lists of Excel shortcuts available on the Internet. Charts
LAMBDA Formulaic Recursion: It’s All About ME! - LAMBDA, now released into the Office 365 Beta world. FUNCTIONS
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
Locating Links #3 - Sometimes deliberate and sometimes inadvertent, external links often work their way into our Excel workbooks 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
Models with S-Curves - An S-curve is simply a graphical depiction of cumulative data for a project 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
Navigation Not Such a Pane - Microsoft has made a “big thing” – quite rightly – about accessibility for all of their products, to ensure they are inclusive to everyone, including more than one billion people with various disabilities around the world. EXCEL
New Data Types and FIELDVALUE Function Now Generally Available (Sort Of) - Understanding the new Data Types in Excel. EXCEL
New Views for Power Query - New updates to Power Query's Get & Transform... POWER QUERY
Not Just Mary Can Have a Little LAMBDA / Excel Hits 500 - Microsoft announced the release of no less than seven new LAMBDA-associated functions (including a landmark 500th one – someone please bake Excel a cake)... FUNCTIONS
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
Pro-Rating Over Time - How to properly Pro-Rate over time... MODELLING
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
Section Numbering - Some very simple tricks can make both you and your colleagues’ lives easier when it comes to building a “Best Practice” model... MODELLING
Self Reviews - This article considers some simple checks you can employ to save face... MODELLING
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.
Summing a Dynamic Range - We often want to sum data for a period of time, e.g. sales for the last quarter, year to date costs and rolling budgets / forecasts EXCEL
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
What about US? Converting Dates to and from US Format - How to convert to and from US date formats... EXCEL TIPS
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