Calculated Items In Pivot Tables

When you've created a pivot table in Excel, you can easily add two fields together and list the result in new field - one not present in your original data source.

1. When you begin the pivot, add the main field from the pivot table you will review data for and a second field which has entries to be tracked for each of the main field items, to the Row area of the field list. In this example, we want to review the groceries sent to three different cities.

2. Next in the pivot table, select a cell with one of the items you want to run a calculation for, and then go the Analyze menu, go to Fields, Items, & Sets . . . Calculated Item.

3. Give a name to a new field, and then select the field that has the items that you want to add up. In the formula box, simply list the items separated by a plus sign. You should see the field whose items are referenced in the formula in the title bar of the dialog box.

4. Finally, pull down a third field into the values area, and set it so a count of that field is generated. The new field, in this example 'Total_Fruit' will show just the sum of the formula you entered.

Naturally, a calculated item can show the result of any number of formulas.

Excel formula to parse out Nth words in cell

You can use this formula to extract the nth word or string in a cell. In this format, the number of the string to be extracted is referenced in cell $B1

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))), (B$1-1)*LEN($A2)+1, LEN($A2)))

Cell $A2 references the cell with multiple strings that is to be reviewed. The formula uses the LEN formula to calculate the position of the string, and the MID formula to extract the string.

Putting this formula in cell B2 and pulling it to the right (CTRL + R), will extract out the nth word in cell A2 which matches the number listed in the first row. So you can designate the number position of the strings you want to extract in successive cells, and pull out the needed data without revising the formula. This works well to separate the words in a person's name into different cells.

Speeding up VLOOKUP

Excel's VLOOKUP formula can run frustratingly slow if you're working with large datasets. It functions by searching each row in an array to see if an exact match can be found when the common VLOOKUP(A2,D:G,4,FALSE) format is used - the FALSE designating an exact match.

The binary version of VLOOKUP using TRUE at the end can work if the data in the searched on array is sorted by the first column. it functions by first checking to see if the searched for value (in the above example A2) is above or below the middle of the search on array. Half of the values to search are ruled out immediately. A VLOOKUP binary search then keeps repeating this process, starting in the middle of one half and excluding a quarter from its review, and so on.

The trouble is that VLOOKUP with the TRUE setting will return the closest possible match - not an exact match. To overcome this limitation, use a VLOOKUP inside a IF . . . THEN formula

=IF(VLOOKUP(A3,I:K,1,TRUE)=A3,VLOOKUP(A3,I:K,3,TRUE),NA())

So instead of this incorrect result returned by a binary VLOOKUP formula:

. . . in the IF . . . THEN formula VLOOKUP first looks to see if the approximate match returns an exact match. If it does it returns that match. If not, it returns a N/A result.

This should function faster than a standard VLOOKUP 'FALSE' formula by several orders of magnitude.