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!

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.


wrote in message
ps.com...
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,

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

Martin Fishlock wrote:
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.


wrote in message
ps.com...
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,

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

Thanks again!

Haas

Martin Fishlock wrote:
Haas,

Try this it is a macro and you place the curser in one of the cells on the
table and it should work. (I had the table in A1:B12 with titles and a free
column to the right for the answers.


Sub lossclaims()
'call it with the curser in on of the cells.

Dim dLossesLimit(1 To 3) As Double ' holds the loss limits
Dim iLossesLimitPtr As Integer ' point to array above

Dim ws As Worksheet ' active worksheet
Dim rTable As Range ' table data used to find rows

Dim iCurYr As Integer ' holds active year

Dim lRow As Long ' current row
Dim lRowStart As Long ' start row of data
Dim lRowEnd As Long 'end row of data
Dim lColYr As Long ' column for year
Dim lColAmt As Long ' column for amount
Dim lColAns As Long ' column for answer

'set up array limits
dLossesLimit(1) = 1000000
dLossesLimit(2) = 500000
dLossesLimit(3) = 75000


Set ws = ActiveSheet
Set rTable = ActiveCell.CurrentRegion ' get the table

lRowStart = rTable.Row
lRowEnd = lRowStart + rTable.Rows.Count - 1

lColYr = rTable.Column ' set the columns
lColAmt = lColYr + 1
lColAns = lColYr + 2

If Not IsNumeric(ws.Cells(lRowStart, lColYr)) Then ' adjust if titles
lRowStart = lRowStart + 1
End If

iCurYr = -1 ' initialise year

For lRow = lRowStart To lRowEnd
If iCurYr < ws.Cells(lRow, lColYr) Then 'changed year reset
iCurYr = ws.Cells(lRow, lColYr)
iLossesLimitPtr = 1
End If
If iLossesLimitPtr < 4 Then ' only three levels
If ws.Cells(lRow, lColAmt) dLossesLimit(iLossesLimitPtr) Then
ws.Cells(lRow, lColAns) = dLossesLimit(iLossesLimitPtr)
iLossesLimitPtr = iLossesLimitPtr + 1
End If
End If
Next lRow

End Sub



--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


" wrote:

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

Martin Fishlock wrote:
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.


wrote in message
ps.com...
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!