I have for the first time tried a nested if as a novice but am now stuck:


I have to enter into a column (say column A) some paid claims figures the
next column B I have to enter some advised claims figures

Under certain contract conditions depending on the size of the numbers I
have to enter (not actually enter but calculate by way of nested if)
Figures into Column C Column D and Column E

The criteria for the contract is (say) $750,000 excess of $250,000

Column C Formula I wrote is
=IF(A1>=1000000,750000,IF(A1<=250000,0,A1-250000))


Because the maximum in the column can be $750,000

Column D Formula I wrote is

=IF(C1=750000,0,IF(B1=0,0,IF(A1+B1>=1000000,750000-C1,A1+B1-250000-C1)))


Column E Formula I wrote is

=C1+D1


My problem is that

if I enter large figures in Column A +B all seems well

But if I enter smaller number say less that $250,000 I am getting negatives
in C&D

I tried to overcome this with the following added to the end of my If
statement in C1
=IF(C1>=0,"",) and also to my nested IF in Column D i.e IF (D1>=0,"",) and
in E '=SUM(C1+D1)=IF(E1>=0,"",)



Where am I going wrong please as an example I am trying to acheive using
the criteria (the insertion of the $750,000 is because it is the maximum
collectable on the contract

Say A entered is $187,914 and B entered is $13,420
C using my formula =IF(A1>=1000000,750000,IF(A1<=250000,0,A1-250000)) gives
0 but
D using my formula
=IF(C1=750000,0,IF(B1=0,0,IF(A1+B1>=1000000,750000-C1,A1+B1-250000-C1)))
gives a minus $48,666

I do not want minus figures to show just a zero

I thought I cured it by putting the smaller IF statements at the end of each
as shown earlier but am getting circulars and also wrong answers can any of
you more experienced kind folk assist.

Much appreciated

Yetti