Calculate per minute cost of cell phone usage

[darkwing_duck]

I have a spreadsheet that I’m trying to use to calculate the cost per
minute of cellular calls. I have 3 plans (in rows 3, 4, and 5) that
each provide a monthly cost (column a), and the number of minutes
included in that cost (column b). There is also a cost per minute if
you go over that allowance (cell E2). I have a field (cell C7) that
I
use to enter the average number of minutes used. I have a drop down
list (cell C8) that allows you to select the plan you have. I have a
formula that is currently written as:

=SUM(((C7-C8)*E2)+A5)/C7 where:


C7 = average number of minutes used
C8 = drop down list to select the number of minutes included in the
plan (list points to B3, B4, or B5)
A5 = cost of the plan


This is written so that if someone uses more minutes than they’re
allowed, it multiplies the number of minutes over what was allowed by
the cost if you go over (cell E2), adds that to the monthly cost,
then
divides the total cost by the total number of minutes used.


My problem is that this is not bulletproof in two ways:


1) The cost of the plan is a variable based on the selection in
C8. So if someone selects the plan defined in B5, then the cost in
cell A5 should be called. If the plan defined in B4 is selected,
then
the cost in cell A4 should be called. If the plan defined in B3 is
selected, then the cost in cell A3 should be called.
2) If the average number of minutes used is less than the
included minutes, then the formula should simply divide the cost by
the number of minutes.


Can someone help me re-write my formula to bulletproof this? I’m
sure
“IF” and “ELSE” is going to be involved, but I don’t have that level
of expertise.


TIA,
Robert

 

[p45cal]

see if this does it for you:
=(MAX(0,C7-C8)*E2+INDEX(A3:A5,MATCH(C8,B3:B5,0)))/C7

make sure costs in column A and cost in cell E2 are all in the sam
units; not one in pounds and the outher in pence (dollars/cents
whatever