If and ANDIF statement

[Mike_Dean]

Hi,

I hope I can explain this accurately enough, I'm calculating telephone call costs given certain call times and cost variables, and I need a formula that looks at the length of the call, the cost of the call, and then calculate if, given the capped call cost, its a capped fee or standard fee


Column B = Length of call
Column D = Cost of call ( which in this case = 0.09 per min

Variables are, if the call cost is less than 15min, the MAX cost is 0.70 eg, 5 min call costs 0.45 (plus a connection cost of 0.06)12 min call costs 0.70 (inc Call Cost)

So as a list of call examples, I want Excel to come up with the same results below.

1Min = 0.15
2Min = 0.24
5Min = 0.51
7Min = 0.69
8Min= 0.70
10Min = 0.70
11Min = 0.70
16Min = 0.79
18Min = 0.97

Overview
Per Min cost = 0.09
Call Cost is Capped at 0.70 for 1st 15 Mins
Connection fee 0.06



Many thanks in advance,
Mike


PS... Using Excel 2007

[daddylonglegs]

If B1 has call length in time format, e.g. 5 minutes is 0:05 then use this formula for cost

=MIN(0.7,B1*1440*0.09+0.06)+MAX(0,B1*1440-15)*0.09

If B1 is just a number, e.g. 5 then change formula to

=MIN(0.7,B1*0.09+0.06)+MAX(0,B1-15)*0.09

[Mike_Dean]

If B1 has call length in time format, e.g. 5 minutes is 0:05 then use this formula for cost

=MIN(0.7,B1*1440*0.09+0.06)+MAX(0,B1*1440-15)*0.09

If B1 is just a number, e.g. 5 then change formula to

=MIN(0.7,B1*0.09+0.06)+MAX(0,B1-15)*0.09

Perfect.. Thank you kindly... didn't think to use the MIN MAX scenario..


Again,
Thanks

[Mike_Dean]

One more if I may..

If C8 = L then return the value from $E$1,

But if C8 = M, N, or O, then return either Mobile, National or Other


This worked if I wanted to return a value, but if I want either a value or Text

=IF(C8="L",$E$1)+IF(C8="N",$E$2*B8)+IF(C8="M",$E$3*B8)+IF(C8="S",$E$4)


I had the text value of Mobile National and Other on cell E2, E3, and E4, but no go...

[NBVC]

Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Mike_Dean]

Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thank you so very much, (apologies for the double post..!

Mike