Hello One and All,

I need help with this IF function problem please. It works in scenario 2 but gives a zero in Scenario 1.

Below are two examples with the different cell amounts and their
units.
NB. After the first cell references C16 and E16 my <(less than symbol) disappears from what i can see right here on this page, however in Excel this doesn`t happen.

Scenario 1:
=IF(C160,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(E160,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...

Gareth,

Both formulae are the same - you can check the business end of the
formula by taking off the IF part so you have SUM((a5)-(a5/a15)*c3)*c5.
The only thing that changes between the scenario 1 and 2 is the tariff
allowance changes from 200 to 400 minutes. With the IF test stripped
out, scenario one will answer with 1.00, change the tariff to 400 and
you will get -3.
It seems to me that what is driving the formula is the contents of
cells C16 and E16 - these are being used as comparators against the
answer of the SUM(...) formula - this is what will cause the answer to
change depending upon what is in these cells

Jon


--
Jon Quixley
------------------------------------------------------------------------
Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
View this thread: http://www.excelforum.com/showthread...hreadid=471276

Hi Jon,

C16 and E16 are the totals boxes for the tariff in Column C and E respectively.

You are 100% correct re what you have stated too.
What is required is:
For example If E16 is less than zero (in this case it is -£3.00) then £0.00 should be displayed. The problem is what is displayed in C16. The logical test answer is £1.00, which is correct but when ENTER is pressed C16 displays £0.00 too. No matter what the value of A15 it always reads £0.00 in C16.
I am using Excel 2000 at work. Would any conflict arise with 2003`s way of inputting formulae?

Best Regards

Gareth...