Tiered Calculated Field w/ Pivot Tables (Excel 2000)

Topic

New Reply

Edited by Steve05 on 27-Dec-01 23:36.

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.

Reply | Quote

Replies

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

Reply | Quote

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…

	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

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

Reply | Quote

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>10000,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.

Reply | Quote

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

Reply | Quote