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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Vicky,

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

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Price Point</td><td style="text-align: center;;">PPM</td><td style="text-align: right;;"></td><td style=";">Period</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Italy</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style=";">Daytime</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Italy</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style=";">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Italy</td><td style="text-align: center;;">1.5</td><td style="text-align: right;;"></td><td style=";">Weekend</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">NAT</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style=";">Daytime</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">NAT</td><td style="text-align: center;;">0.75</td><td style="text-align: right;;"></td><td style=";">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">NAT</td><td style="text-align: center;;">0.25</td><td style="text-align: right;;"></td><td style=";">Weekend</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">UK</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style=";">Daytime</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">UK</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style=";">Evening</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">UK</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style=";">Weekend</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Tarriff</p><br /><br />

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.
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Price Point</td><td style="text-align: right;;"></td><td style=";">Period</td><td style="text-align: right;;"></td><td style=";">PPM (Regular)</td><td style=";">PPM (Array)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">NAT</td><td style="text-align: right;;"></td><td style=";">W</td><td style="text-align: center;;"></td><td style="text-align: center;;">0.25</td><td style="text-align: center;;">0.25</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">UK</td><td style="text-align: right;;"></td><td style=";">E</td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Italy</td><td style="text-align: right;;"></td><td style=";">D</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">NAT</td><td style="text-align: right;;"></td><td style=";">E</td><td style="text-align: center;;"></td><td style="text-align: center;;">0.75</td><td style="text-align: center;;">0.75</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">NAT</td><td style="text-align: right;;"></td><td style=";">D</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Calls</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Tarriff!I:I,MATCH(<font color="Green">H2,Tarriff!H:H,0</font>)+IF(<font color="Green">J2="E",1,IF(<font color="Purple">J2="W",2,0</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Tarriff!I$2:I$10000,MATCH(<font color="Green">H2&J2,Tarriff!H$2:H$10000&LEFT(<font color="Purple">Tarriff!K$2:K$10000,1</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Hope that helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top