Nearly There on nested if...but stuck

[yetti]

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

 

[daddylonglegs]

You could simplify the formula in C1 to

=MEDIAN(A1-250000,750000,0)

I'm not 100% sure what you're trying to do with D1 but it looks like
you could do something similar, i.e.

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

 

[yetti]

Thanks for that response DaddyLongLegs
I am new at Excel and do not understand the function MEDIAN but long ago at
school from memory I thought it was to do with averages. Can you describe it
a bit more please. how it works here?....Thanks for your patience.

With D I am trying to do the following
A is a column that lists all amounts of claims actually paid on a loss in
cash terms to a person.
B is a column showing additional further amounts claimed by the person that
one day will have to be paid.

This is the source data of all relating to that loss

There is then the application of the terms of the contract in C&D relating C
on Actually Paids (A) and D on further amounts claimed (B) E=C&D but cannot
exceed $750,000

Contracts vary but the example I gave was that whatever if the value in A
was the maximum collectable is $750,000
Equally the Columns C&D do not start to kick in until $250,000 has been
recorded.

So I think (cast your eye over it as you will know better) that my formula
catered for that.Your simpler one may equally be doing the same thing but
could you spell it out a little better for my simple mind if it is not
asking too much.

So sometimes if Loss larger than $750,000 plus kick in point $250,000 the
Column C becomes $750,000 Column D becomes Nil and E is the sum of C&D so
the contract is exhausted

If a Loss is in Col A say 500,000 C will be 500,000 less kick in point of
$250,000 = $250,000

If in Column B there is recorded data that goes into D until such times as
C&D summed in E = 750,000

See for example below Example 1&2 are typical when actual paids exceed
$250,000 with D showing the growth in losses yet to be paid at a future date
3 shows exhausted contract even if A was $2m C would be $750,000 D would be
nil as no point in adding further potentials to pay
A B C D E
$ 331,125 $ 11,087 $ 81,125 $ 11,087 $ 92,212



$ 294,824 $ 216 $ 44,824 $ 216 $ 45,040


$1,000,000 $ - $ 750,000 $ - $
750,000

I was also trying to keep a log of how things coming in were growing in
example below which has not yet reached $250,000 in A If my first formula
was used it gives a negative in C that I wanted to show as 0

$ 187,914 $ 13,420 $ - $ -
-


I hope this is not too vast a tome to follow DaddyLongLegs your additional
thoughts or comments are much appreciated

Thanks Yetti

"daddylonglegs" <[email protected]>
wrote in message

 

[yetti]

Thanks for that response DaddyLongLegs
I am new at Excel and do not understand the function MEDIAN but long ago at
school from memory I thought it was to do with averages. Can you describe it
a bit more please. how it works here?....Thanks for your patience.

With D I am trying to do the following
A is a column that lists all amounts of claims actually paid on a loss in
cash terms to a person.
B is a column showing additional further amounts claimed by the person that
one day will have to be paid.

This is the source data of all relating to that loss

There is then the application of the terms of the contract in C&D relating C
on Actually Paids (A) and D on further amounts claimed (B) E=C&D but cannot
exceed $750,000

Contracts vary but the example I gave was that whatever if the value in A
was the maximum collectable is $750,000
Equally the Columns C&D do not start to kick in until $250,000 has been
recorded.

So I think (cast your eye over it as you will know better) that my formula
catered for that.Your simpler one may equally be doing the same thing but
could you spell it out a little better for my simple mind if it is not
asking too much.

So sometimes if Loss larger than $750,000 plus kick in point $250,000 the
Column C becomes $750,000 Column D becomes Nil and E is the sum of C&D so
the contract is exhausted

If a Loss is in Col A say 500,000 C will be 500,000 less kick in point of
$250,000 = $250,000

If in Column B there is recorded data that goes into D until such times as
C&D summed in E = 750,000

See for example below Example 1&2 are typical when actual paids exceed
$250,000 with D showing the growth in losses yet to be paid at a future date
3 shows exhausted contract even if A was $2m C would be $750,000 D would be
nil as no point in adding further potentials to pay
A B C D E
$ 331,125 $ 11,087 $ 81,125 $ 11,087 $ 92,212



$ 294,824 $ 216 $ 44,824 $ 216 $ 45,040


$1,000,000 $ - $ 750,000 $ - $
750,000

I was also trying to keep a log of how things coming in were growing in
example below which has not yet reached $250,000 in A If my first formula
was used it gives a negative in C that I wanted to show as 0

$ 187,914 $ 13,420 $ - $ -
-


I hope this is not too vast a tome to follow DaddyLongLegs your additional
thoughts or comments are much appreciated

Thanks Yetti

"daddylonglegs" <[email protected]>
wrote in message

 

[daddylonglegs]

Yetti

Median gives you the middle number so

=MEDIAN(0,4,20)

gives 4

In your case you effectively want the middle number from

0, 750000 and A1-250000

i.e. if A1-250000 is less than zero you get zero, If A1-250000 is
greater than 750000 you get 750000, otherwise you get A1-250000.

I agree that your formula will get the same results, I was simply
attempting to give you a shorter alternative but, of course, it's
preferable if you understand it if you'll need to alter it in the
future.

From what you say and your examples it seems to me that in D1 you
simply need

=MIN(B1,750000-C1)

 

[yetti]

Hi DaddyLongLegs
Extremely good of you to come back so quickly. Thanks for the clear
explanation of Median to me I will try out your other formula as well. I
will get there (Excel Savvy) bit by bit but would not have got anywhere
without yours and Ron Rosenfelds kind assistance. This News Group is a
lifeline to us new Excel tryers.
Regards
Yetti

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...