Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

calculation pivot tables

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Calculation is pivot tables + nested offsets

Calculation is pivot tables + nested offsets

ResolvedVersion 2007

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

Edited on Mon 10 Feb 2014, 18:20

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

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Remove unused toolbar buttons

Are there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following:

1. Go to Tools - Customise - Commands.
2. Select the toolbar button you wish to remove, then use your mouse to drag and drop the button into the Excel window. When you release your finger from the mouse, the button will disappear.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.