# Hans de cocq

## 290 Flips | 6 Magazines | 2 Likes | 2 Following | 919 Followers | @hansdecocq | Keep up with Hans de cocq on Flipboard, a place to see the stories, photos, and updates that matter to you. Flipboard creates a personalized magazine full of everything, from world news to life’s great moments. Download Flipboard for free and search for “Hans de cocq”

### Excel Create an Amortization Table

Create an Amortization Table<p>Problem: I know it is easy to figure out a monthly payment using PMT. I would like to see my loan balance after each …

Analytics### Excel Calculate Invoice Due Dates

Calculate Invoice Due Dates<p>Problem: I have a spreadsheet of payables. I need to sort by Due Date, but all I have is invoice date and terms.<p>Figure 546 …

Project Management### Excel Calculate Receivable Aging

Calculate Receivable Aging<p>Problem: I have a worksheet showing open invoices. I want to calculate how many days old each unpaid invoice is.<p>Figure 548 …

receivable### Excel Calculate a Moving Average

Calculate a Moving Average<p>Problem: I have 36 months of sales data. In order to create a prediction of sales, I want to calculate a three-month moving …

Analytics### Excel Whatever Happened to the @@ Function?

Whatever Happened to the @@ Function?<p>Problem: Back in Lotus 1-2-3, there was an @@ function. If you used @@(A3), Lotus would go to A3. A3 was …

Data Visualization### Excel Convert PowerPivot to Formulas

Convert PowerPivot to Formulas<p>A few pages ago, I talked about all the bad side-effects of having PowerPivot use OLAP pivot tables. Here is one …

Analytics### Excel Use Validation to Create Dependent Lists

Use Validation<br>to Create Dependent Lists<p>Problem: I want to create two dropdown lists. The second list should be dependent on what is selected in the …

dependent### Excel Sum All of the Lookups

Sum All of the Lookups<p>Problem: Are there any other arcane tricks with the old LOOKUP function that you can use to close out this string of topics on …

Arcane### Excel Refer to a Related Table in a Formula

Refer to a Related Table in a Formula<p>Problem: I am entering a formula in the Sales table. I need to lookup a value from Rates table. I’ve already …

Databases### Excel Combine Two Lists Using VLOOKUP

Any customers with #N/A are new customers and need to be added to the list of customers in column A. If you get a few #N/A values, sort by column F …

Analytics### Excel Combine Formulas into a Mega-Formula

Combine Formulas into a Mega-Formula<p>Problem: When I need to build a complex calculation, I sometimes need to build several intermediate formulas to …

Analytics### Excel Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?

Whatever Happened to Multiple<br>Consolidation Ranges in Pivot Tables?<p>Problem: I read your book Pivot Table Data Crunching, which describes an awesome …

Analytics### Excel Calculations Outside of Pivot Tables

What is GETPIVOTDATA and how did it get in your worksheet? The Excel team is hoping that you would see GETPIVOTDATA, then go find out what it is and …

Analytics### Excel Report Text in a Pivot Table

Report Text in a Pivot Table<p>Problem: I have a list of text entries. I want to use slicers to filter this list.<p>Figure 935 Filter with …

Analytics### Excel Fast Multi-Column VLOOKUP

Fast Multi-Column VLOOKUP<p>Problem: I have to do twelve columns of VLOOKUP. The lookup table is large. The data set is even larger. It is taking …

Analytics### Excel Build a Better Top Five with A Filter Hack

Build a Better Top Five with A Filter Hack<p>Problem: Can you AutoFilter a pivot table? If you could turn on the AutoFilters and then filter to the top …

Analytics### Process Lasso and Windows 10

Process Lasso 8.2 was released a few days ago and has full Windows 10 support. There weren’t many changes necessary, as we were already prepared. …

### Excel Show Two Values in a Split Cell

Show Two Values in a Split Cell<p>Problem: My manager wants me to show two values in one cell, as shown here.<p>Figure 1156 One cell shows 33 & …

Data Science### Excel Daily News

Would you like to see your content in the Excel Daily News? Tweet a link to the article and include the #Excel hashtag. And for more tips and tricks, …