Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Loss calculations
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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hardship letter explaining devastating loss property loss financi | Excel Discussion (Misc queries) | |||
Data loss!!!! | Excel Discussion (Misc queries) | |||
Loss of data in very large calculations (vba in excell) | Excel Programming | |||
IF f1G1;Win;loss | Excel Worksheet Functions | |||
Weight loss line chart to monitor weight loss progress | Charts and Charting in Excel |