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 in landline calls peak and off peak, same network calls peak and off peak, cross network calls peak and off peak and voicemail calls. These are fields A5 to A11 respectively. MMS pics and video usage are in A12 and 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 less 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 of -£3.00 is produced! Well quite clearly service providers don`t pay us if we don`t use our allocated call allowance! The answer if less than zero should read "£0.00". This entire formula produces zero whether the answer is greater than zero or not.

My main wish is to have the above sum coupled with all the other call possibilities and after that have text message and multi-media messages 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 on the best tariff to suit our current call usage.

Thanks for your time....

Gareth

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.

--
Regards,
Fred


"Gareth Trow" wrote in message
...

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 in
landline calls peak and off peak, same network calls peak and off peak,
cross network calls peak and off peak and voicemail calls. These are
fields A5 to A11 respectively. MMS pics and video usage are in A12 and
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 less
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 of
-£3.00 is produced! Well quite clearly service providers don`t pay us
if we don`t use our allocated call allowance! The answer if less than
zero should read "£0.00". This entire formula produces zero whether the
answer is greater than zero or not.

My main wish is to have the above sum coupled with all the other call
possibilities and after that have text message and multi-media messages
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 on
the best tariff to suit our current call usage.

Thanks for your time....

Gareth


--
Gareth Trow

Hi Fred,

Thanks for your reply. The formula shown in this page
=IF(U20,0,0,SUM((A5)-(A5/A15)E3)*E5) is different to the one given in my outlook express message which was:
=IF(U2<0,0,SUM((A5)-(A5/A15)E3)*E5)
After trying both the outlook formula worked.

However, when this formula was used on another lower priced tariff it still gave a zero figure when a chargeable amount should have been given. The formula checker shows the correct amount but shown in the cell is £0.00.
Below are two examples with the different cell amounts and their units.

Scenario 1:
=IF(C16<0,0,SUM((A5)-(A5/A15)C3)*C5)
C16 is the formula cell only
A5 is the input cell and is 50 minutes of talk time used to land lines peaktime,
A15 is 250 minutes of total talk time used for all types of calls,
C3 is the tariff call allowance of 200 minutes
C5 is £0.10 the call cost for land lines at peak time per minute
This should give an answer of £1.00 (but shows £0.00)

Scenario 2:
=IF(E16<0,0,SUM((A5)-(A5/A15)E3)*E5)
E16 is the formula cell only
A5 is the input cell and is 50 minutes of talk time used to land lines peaktime,
A15 is 250 minutes of total talk time used for all types of calls,
E3 is the tariff call allowance of 400 minutes
E5 is £0.10 the call cost for land lines at peak time per minute
This should give an answer of £0.00 (and does)

Circular reference keeps flagging up but when i check the formula with the logical test it gives the correct answer everytime. When i close the formula check box £0.00 is shown in the cell regardless of whether the value is higher than zero or not. Very confusing and totally beyond my knowledge. Any further help would be greatly appreciated.

Best Regards

Gareth...

Ive just read the message i posted and the formulae shown in the first paragraph are identical however the less than symbol in the second formula has been replaced with a zero!

The first thing we need to do is find out exactly what formula you are using.
The ones you are posting will result in syntax errors in Excel. The portion
'(a5/a15)e3' has no operator before the 'e3'. Secondly, take out the SUM
function -- it's unnecessary as you aren't summing anything.

Do you mean:

=if(u2<0,0,(a5-(a5/a15)*e3)*e5)

?
--
Regards,
Fred


"Gareth Trow" wrote in message
...

Hi Fred,

Thanks for your reply. The formula shown in this page
=IF(U20,0,0,SUM((A5)-(A5/A15)E3)*E5) is different to the one given in
my outlook express message which was:
=IF(U2<0,0,SUM((A5)-(A5/A15)E3)*E5)
After trying both the outlook formula worked.

However, when this formula was used on another lower priced tariff it
still gave a zero figure when a chargeable amount should have been
given. The formula checker shows the correct amount but shown in the
cell is £0.00.
Below are two examples with the different cell amounts and their
units.

Scenario 1:
=IF(C16<0,0,SUM((A5)-(A5/A15)C3)*C5)
C16 is the formula cell only
A5 is the input cell and is 50 minutes of talk time used to land lines
peaktime,
A15 is 250 minutes of total talk time used for all types of calls,
C3 is the tariff call allowance of 200 minutes
C5 is £0.10 the call cost for land lines at peak time per minute
This should give an answer of £1.00 (but shows £0.00)

Scenario 2:
=IF(E16<0,0,SUM((A5)-(A5/A15)E3)*E5)
E16 is the formula cell only
A5 is the input cell and is 50 minutes of talk time used to land lines
peaktime,
A15 is 250 minutes of total talk time used for all types of calls,
E3 is the tariff call allowance of 400 minutes
E5 is £0.10 the call cost for land lines at peak time per minute
This should give an answer of £0.00 (and does)

Circular reference keeps flagging up but when i check the formula with
the logical test it gives the correct answer everytime. When i close
the formula check box £0.00 is shown in the cell regardless of whether
the value is higher than zero or not. Very confusing and totally beyond
my knowledge. Any further help would be greatly appreciated.

Best Regards

Gareth...

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

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

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

--
Regards,
Fred


"Gareth Trow" wrote in message
...-

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
in
landline calls peak and off peak, same network calls peak and off
peak,
cross network calls peak and off peak and voicemail calls. These are
fields A5 to A11 respectively. MMS pics and video usage are in A12
and
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(U20,"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 less
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
of
-£3.00 is produced! Well quite clearly service providers don`t pay us
if we don`t use our allocated call allowance! The answer if less
than
zero should read "£0.00". This entire formula produces zero whether
the
answer is greater than zero or not.

My main wish is to have the above sum coupled with all the other call
possibilities and after that have text message and multi-media
messages
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 on
the best tariff to suit our current call usage.

Thanks for your time....

Gareth


--
Gareth Trow-


--
Gareth Trow

Hi Fred,

Yes the operator before the e3 is * (asterisk)
After the first cell reference is < (less than symbol), which appears on these threads as a zero for some reason.

I`ve also removed the SUM from my formula.
Scenarios 1 and 2 on the previous reply are two of the actual examples which should result in different costs.

Best Regards

Gareth...