Excel to re-cost call usage..

vickitoria

New Member
Joined
Sep 20, 2014
Messages
1
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Vicky,

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


Excel 2007
HIJK
1Price PointPPMPeriod
2Italy3Daytime
3Italy2Evening
4Italy1.5Weekend
5NAT1Daytime
6NAT0.75Evening
7NAT0.25Weekend
8UK4Daytime
9UK2Evening
10UK1Weekend
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
HIJKLM
1Price PointPeriodPPM (Regular)PPM (Array)
2NATW0.250.25
3UKE22
4ItalyD33
5NATE0.750.75
6NATD11
Calls
Cell Formulas
RangeFormula
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,305
Messages
5,836,534
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top