Calculating best mobile phone price plan

Y

yutingkuo

My mobile operator lets me download a CSV file containing eac
call/SMS/data I made in the phone bill. I want to evaluate what m
phone costs will be if I chose a different price plan. The spreadshee
looks like:

| Type | Date | Time | Number | Duration | Cost |

Now I want to add extra cost columns each representing a differen
price plan. So it would look up the date/time to decide if it's
peak/offpeak/weekend tariff, extract the price from another tarif
table.

So at the bottom you can have the total monthly cost of each price pla
and decide which plan is the cheapest.

For now I can only see using VBA to go through each line and look u
the tariff table - would it be possible to use functions/filters etc..
to automatically calculate the cost
 
J

jeff

HI,

Not sure I understand what all you have to lookup, but
there is a VLookup (and Hlookup if you need) function
that can look up a value in a range of cells and return
a value. You may not need VBA at all.

take a look.
jeff
 
M

Myrna Larson

Seems you could have just one table, with the beginning of the time periods in
the 1st column, and add a line at the end for the weekend rate, and the prices
for the various plans in columns 2, 3, 4, etc. Assuming you name that range
RateTable, then the formula to get the rate for plan 2 would be

=VLOOKUP(IF(WEEKDAY(B2,1)>5,"WE",C2),RateTable,2)
 
Top