Loss calculations

[haas786]

Hi all,

I've encountered a problem which I can't solve in Excel. Can someone
please help with this?

I have 2 columns: A and B. A contains Year information, while column B
contains Losses, e.g.:

Year Losses
1980 45
1980 500
1980 6,500
1981 1,002
1981 2,150,000
1981 6,500,000
1982 1,350,000
1982 490,650
1982 950,000
1983 56,000
1983 656,580
Now here's what I need - as you can see, the same year can appear more
than once. This means that more loss driving events happen in that
year. So, I need to look in every year in top to bottom order (without
going back up the cells) and satisfy the following conditions:

1. If a loss greater than $1,000,000 occurs, make the adjacent cell in
the next column C have a max loss of 1,000,000. Once that condition is
met, you got to step 2

2. Now that $1,000,000 loss has occured, the next loss in that SAME
year greater than 500,000 should have in an adjacent cell in the next
column C a max loss of 500,000. If that does happen, we go to step 3...

3. Once you have used up the $1,000,000 and $500,000 max numbers in the
SAME year, the next one to look out for is if there is a loss greater
than $75,000 and then put a max loss of 75,000 into the adjacent cell
in column c. Once this happens you stop doing that for that particular
year.

e.g.:

In 1981 above, the first loss greater than 1,000,000 (step 1) is
2,150,000, so right next to that cell, I want to place a max loss of
1,000,000. Go down the list and find the next loss in 1981 greater than
500,000 (step 2) - that corresponds to 6,500,000. Place max loss of
500,000 right next to that cell. Now, 1981 ends, so you have to start
the process again for 1982 by looking for 1,000,000 loss. It should
look like this for 1982:

Year Losses Column C
1982 1,350,000 1,000,000
1982 490,650
1982 950,000 500,000
1983 56,000
1983 656,580 75,000



Sorry for the long winded explanation, but I hope this illustrates what
I am looking for. Any or all help will be greatly appreciated!

Thanks!

 

[Martin Fishlock]

Haas:

Can you confirm that the 1983 losses are correct in your example solution. I
think the claimable loss should be 500,000 and not 75,000 as the first
claimable amount is over 500,000 in that year.

Please confirm.

 

[haas786]

Martin,

Oops, I screwed up that example for 1983. In this particular example,
nothing will happen. A loss greater than 1,000,000 has to happen first
and a max of 1,000,000 would be assigned to it. Next, a loss greater
500,000 has to happen to be assigned a 500K max. And, finally a loss of
75K follows. This would only apply on a year by year basis and resets
the following year. So, in 1983, there is no claimable loss because the
first event (>1,000,000) hasn't occurred yet.

Thanks for your help in advance,

Haas

 

[haas786]

Martin,

You are the man! Thank you very, very much for this! I made a few
modifications and it works great!

Thanks again!

Haas