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
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
GF Index Match.xlsx
