Cell phone tariff checker design help

[Gareth Trow]

I am producing a mobile phone tariff checker and need some help please.

Column A is dedicated to variable input fields and show minutes used i
landline calls peak and off peak, same network calls peak and off peak
cross network calls peak and off peak and voicemail calls. These ar
fields A5 to A11 respectively. MMS pics and video usage are in A12 an
A13. Text message usage is inputed into A14.
The following sum applies to landline calls at peak time only:

=SUM((A5)-(A5/A15)*E3)*E5=IF(U2<0,"0")
where A5 is landline peak time calls in minutes
A15 is total minutes used in one month ie =SUM(A5:A11)
E3 is the tariffs free call allowance to all networks in minutes
E5 is the unit cost to call landlines at peak times in pounds
The IF function is attached because if the monthly usage is far les
than the tariff alowance it produces a negative value in pounds
i.e. where A5 is 50, A15 is 250, E3 is 400 and E5 is £0.10 a figure o
-£3.00 is produced! Well quite clearly service providers don`t pay u
if we don`t use our allocated call allowance! The answer if less tha
zero should read "£0.00". This entire formula produces zero whether th
answer is greater than zero or not.

My main wish is to have the above sum coupled with all the other cal
possibilities and after that have text message and multi-media message
added to it too.

If we could get started on this i`m sure we will achieve the result
Once it`s finished i can give you all a copy and we will always be o
the best tariff to suit our current call usage.

Thanks for your time....

Garet

 

[Fred Smith]

Your problem most likely is the placement if the IF statement. See if the
following solves your problem:

=if(u2<0,0,SUM((A5)-(A5/A15)*E3)*E5)

If not post back and we'll help you further.