I have a spreadsheet that I=92m 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:

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


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


This is written so that if someone uses more minutes than they=92re
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=92m
sure
=93IF=94 and =93ELSE=94 is going to be involved, but I don=92t have that le=
vel
of expertise.


TIA,
Robert

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 same
units; not one in pounds and the outher in pence (dollars/cents,
whatever)


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=163930

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]