Nearly There on nested if...but stuck

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

On Sat, 04 Feb 2006 08:00:17 GMT, "yetti" <[email protected]> wrote:

>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
>
>
>

I'm not sure exactly what you are trying to do.

It is clear that the maximum payout is $750,000 and the minimum payout is $0.

If it is also the case that payouts are calculated, within those constraints,
by subtracting $250,000 from the number in A1, then:

=MAX(0,MIN(A1-250000,750000))

should accomplish your purpose.


--ron

Hi Ron,
Your formula is very useful and certainly works well for column C.
Thank you for taking the time to assist me.

DaddyLongLegs has also been a kind soul with his assistance...I sent another
post clarifying what I fully wanted to acheive any further thoughts after
reading that one?

Once again thanks to Both for such quick results much appreciated

Regards

Yetti


"Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> On Sat, 04 Feb 2006 08:00:17 GMT, "yetti" <[email protected]> wrote:
>
>>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
>>
>>
>>
>
> I'm not sure exactly what you are trying to do.
>
> It is clear that the maximum payout is $750,000 and the minimum payout is
> $0.
>
> If it is also the case that payouts are calculated, within those
> constraints,
> by subtracting $250,000 from the number in A1, then:
>
> =MAX(0,MIN(A1-250000,750000))
>
> should accomplish your purpose.
>
>
> --ron

On Sat, 04 Feb 2006 13:42:31 GMT, "yetti" <[email protected]> wrote:

>Hi Ron,
>Your formula is very useful and certainly works well for column C.
>Thank you for taking the time to assist me.
>
>DaddyLongLegs has also been a kind soul with his assistance...I sent another
>post clarifying what I fully wanted to acheive any further thoughts after
>reading that one?
>
>Once again thanks to Both for such quick results much appreciated
>
>Regards
>
>Yetti

I do not understand the purpose of Column D. It doesn't seem to be necessary
to get the result in Column E.

However,

C: =MAX(0,MIN(A1-250000,750000))

E: =MIN(B1+C1,750000)

gives the same answers you show in your example.
--ron