Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tiered Calculated Field w/ Pivot Tables (Excel 2000)

    <P ID="edit" class=small>Edited by Steve05 on 27-Dec-01 23:36.</P>I want to create a tiered calculated field inside of a pivot table. (Almost like a Select/Case Controlling procedure in VBA). I can use an If Function but that only goes as far as one tier. Is there any way that I can string multiple if's along? Or perhaps use a different procedure. I'm trying to stay away from using VBA, but will appreciate any help.

    I'm sorry. I should have explained myself better. I have a pivot table that shows my reps and their sales totals. I want to calculate commissions depending on the value of their total sales. So if a rep made between 20,000 and 40,000 they get a 6% bonus; if they made between 41,000 and 50,000, then they get a 10% bonus. I need up to four tiers. Again, any help is appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Tiered Calculated Field w/ Pivot Tables (Excel 2000)

    I didn't quite get how this would fit into a pivot table, but it is very easy to nest IF statements, as in

    =IF(E4<20000,0,IF(E4<40000,0.06,IF(E4<50000,0.1,0. 15)))*E4

    Assuming that the sales figure is in cell E4, This would give you
    0% below 20,000
    6% from 20,000 to 40,000
    10% from 40,000 to 50,000
    15% above 50,000

    StuartR

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Tiered Calculated Field w/ Pivot Tables (Excel 2000)

    I have this habit of answering the question that is asked, rather than solving the problem!

    You certainly can nest IF statements, as in my previous reply, but It would probably be much more sensible for you to use HLOOKUP or VLOOKUP as in...

    <pre> E F G H

    4 25000 0 0
    5 0.06 20000 0.06
    6 40000 0.08
    7 50000 0.1
    8 90000 0.15
    </pre>


    E4 Contains the Sales figure
    The array at G4:H8 shows the commission rates for various sales amounts
    E5 has the formula =VLOOKUP(E4,G4:H8,2,TRUE)

    StuartR

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Tiered Calculated Field w/ Pivot Tables (Excel 2000)

    Hi Stephen,
    You could do this by inserting a calculated field in your pivot table with a formula something like:
    = IF(sales>40000,0.1,IF(sales>20000,0.6,IF(sales>100 00,0.4,0.3)))
    You can change the values as appropriate. Unfortunately you don't seem to be able to use lookups in calculated fields as you can't use arrays, names or references, so you will have to hard code your values within the formula.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tiered Calculated Field w/ Pivot Tables (Excel 2000)

    Thanks so much. I'm sure that this is going to work. I love this Lounge!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •