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 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
- Achieving the Impossible: Reversing Text — I would like to discuss a key issue frequently encountered in Excel... Excel, Functions
- AGGREGATE — Most complex function ever..? Functions
- Aggregating Aggravating Time Periods — How to model debt repayment calculations transparently, without using Add-In functions. 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
- Chart Labels — How to move the chart labels in mysterious ways. Charts
- Charts and Dashboards Blog Series — The compilation of all the Charts and Dashboards blogs we have published so far!... Charts, Excel Tips
- 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
- Counting Unique Items in a List — When working with data, you often need to know how many unique items you have in a list Excel Tips
- 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 Lookups — This time, we take a familiar modelling scenario and, er, “extend” it. Applied Excel, Modelling, Data Tables
- 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
- Extracting Pictures from Excel — Excel is frequently used to create charts and graphs. Sometimes we need to save these visualisations as files for board reports or images on websites, which may prove a little awkward when there are many charts to save. Applied Excel
- 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
- GROUPBY, PIVOTBY, PERCENTOF, ETA LAMBDAS ET AL — Microsoft have added three new functions and “eta reduced lambda” functions (known as “eta lambdas”) into the Excel family... Excel, Functions
- 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
- LIFO Parole — I have been inundated with people asking me: how do you model on a Last In, First Out (LIFO) basis? Applied Excel, Modelling
- 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
- Opting for Optimised Economic Life — When modelling for accounting purposes, sometimes you need to verify the life chosen for depreciation purposes... Modelling, Applied Excel
- Order of Operations — Understanding the order of operations in Excel to avoid potential errors... Modelling
- Pivotal PivotTables — How to use PivotTables to turn data into information. Data
- Power BI Tips Blog Series — The compilation of all the Power Pivot blogs we have published so far!... Power Bi
- 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
- Simple Pro-Rating Over Time — Some time ago, I wrote about a problem of pro-rating amounts over time, as costs (or revenues for that matter) and reporting dates seldom coincide. In hindsight, I may have over-complicated the fundamental problem by introducing full-time versus part-time and inflationary factors. Applied Excel, 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
- Slicing One Element on a Chart Only — Consider a scenario where create a chart, say, a line chart, but you only want one element (one of the lines) to be modified using a slicer. Charts, Excel Tips
- 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
- 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
- Sumproduct Squared..? — Well, Sumproduct by SumProduct must be Sumproduct squared, yes..?
- Tables — Summary of the new feature introduced in Excel 2007. Data
- Take it to the Limit — And I can't tell you why... Excel Tips
- Taking Stock of Inventory — How to properly calculate movements in inventory and associated cost of of goods sold... Modelling
- 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 answer to everything – It Depends! Dependent Data Validation lists for many sets of input cells — We will show you how to set up Data Validation drop-downs that work with each other.
- The Check’s in the Post — We Heard you like checks so we put a check in your checks... Applied Excel
- Time for Payback — In these uncertain times it is more important than ever to keep track of your cashflow. Applied Excel, Modelling
- 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
- Weighted Averages Await — Sometimes, Excel problems are like buses. You don’t see a particular problem for a while and then suddenly, several come along, almost at the same time. Applied Excel, Modelling
- 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
