Excel to re-cost call usage..

[vickitoria]

I'm using excel at the moment to recost call usage. Calls are split by D/E/W and by the pricepoint within the tariff. So each price point (EG Calls to Turkey) would have three rates, one for each D, E, W. At the moment this is done by sorting the tariff so all D, E & W rates are together, then doing a lookup against all the D calls, one for all the E calls and one for all the W calls to bring back the relevent ppm rate. I'm thinking there must be a better way of doing this. There isn't the same number of rows in every tariff either, so theres no guarantee that all the daytimes rates would be in the same row every time.

Column J is D/E/W, H is the pricepoint (going to use NAT for my example below). What I want to do is -

J2 = D, H2 = NAT, bring back the PPM rate for NAT in column H of the "Tariff" tab which has "DAYTIME" in column K. (Or if J2 = E, H2 = NAT, bring back the PPM rate for NAT in Column H of the "Tariff" tab which has "Evening" in column K)

I'm no stranger to excel but this one is stumping me. My colleagues generally do this using a printed copy of the tariff sheet and manually inputting each rate, so I'm trying to make life a bit simpler for them before I leave in a couple of weeks...

Any help or advice is appreciated.

Thanks,

Vicky

 

[Snakehips]

Vicky,

Welcome to MrExcel.
As far as I understand what you are asking, maybe something like.....

Excel 2007
	H	I	J	K
1	Price Point	PPM		Period
2	Italy	3		Daytime
3	Italy	2		Evening
4	Italy	1.5		Weekend
5	NAT	1		Daytime
6	NAT	0.75		Evening
7	NAT	0.25		Weekend
8	UK	4		Daytime
9	UK	2		Evening
10	UK	1		Weekend
Tarriff

Two options. Use the regular formula if the D, E, W rates for each price point in the tariff sheet are always going to be in D, E, W order. Or use the Array formula (Ctrl + Shift + Enter) if the tariff list is randomly sorted.

Excel 2007
	H	I	J	K	L	M
1	Price Point		Period		PPM (Regular)	PPM (Array)
2	NAT		W		0.25	0.25
3	UK		E		2	2
4	Italy		D		3	3
5	NAT		E		0.75	0.75
6	NAT		D		1	1
Calls

Cell Formulas
Range	Formula
L2	=IFERROR(INDEX(Tarriff!I:I,MATCH(H2,Tarriff!H:H,0)+IF(J2="E",1,IF(J2="W",2,0))),"")
M2	{=IFERROR(INDEX(Tarriff!I$2:I$10000,MATCH(H2&J2,Tarriff!H$2:H$10000&LEFT(Tarriff!K$2:K$10000,1),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.