Calculation is pivot tables + nested offsets | Excel forum

Resolved · High Priority · Version 2007

Mon 10 Feb 2014, 16:39 Delegate Alberto said...

Alberto has attended:
Excel Advanced course

Calculation is pivot tables + nested offsets

After creating a Pivot table under "option" I can do "calculation" (set fields). How does that work? I would really like to know this in detail.
If I do a lookup, how do I use the offset formula within the lookup formula? How does the offset formula work? E.g. In the table below I want intead of the points under the heading 2000, the ones 2 cells at the right (under 10000). The formula I worte is =hlookup(f30,d24:j26,offset(f24,1,2,0,0),true). Why it does not work? Which would be the right formula?

ref. d e f g h i j
20
21 BAND WIDTHS 2000-4999 10000-24999 50000+
22 0-1999 5000-9999 25000-49999
23
24 Sales £ 0 2000 5000 10000 25000 50000
25 Points 0 2 4 6 10 15
26 Commission £0 £20 £50 £100 £250 £500
27
28
29
30 Enter Sales -> 2000
31
32 Points #REF!
33 Commission 20
34

Mon 10 Feb 2014, 18:18 Edited on Mon 10 Feb 2014, 18:20 Trainer Gary said...

RE: calculation is pivot tables + nested offsets

Hi Alberto

Thanks for getting in touch.

With regards to PivotTable calculations, when you go to Formulas > Calculated Field you can create a new field based off of existing PivotTable fields. So you can take one field and multiply or subtract it for example, or manipulate multiple fields into a single field.

With Calculated Items you can calculate with entries from a field, adding items to create a new item for example. See the attachment for some examples of this.

With regard to your HLOOKUP + OFFSET question, OFFSET isn't quite the right function here. OFFSET returns a cell reference.

A better fit would be INDEX and MATCH. INDEX returns a value in an array (a bit like VLOOKUP/HLOOKUP) and MATCH returns where it found something.

The formula you need will be something like this:

=INDEX(D25:I25,MATCH(F30,D24:I24,1)+2)

Which says: in the range D25:I25, find the number "2000" in the row above, then move along 2 cells. If 2000 is not found, round down.

I've attached both of these in an example spreadsheet.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

GF Index Match.xlsx

Mon 17 Feb 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Activating the formula bar with the keyboard in Excel 2010

The formula bar in Excel 2010 contains a number of different formulas. However, it is time consuming to open the formula bar every time and insert the formula using the mouse. Instead, you can activate the formula bar with the keyboard. Simply press the F2 button on the keyboard then the information in the cell will expand allowing you to edit the information in the cell.

View all Excel hints and tips