I am fairly new to advanced formula use in Excel, and I am stumped. I use index match all the time and the usual sum ifs, ifs, and etc...but can't figure this out. My boss is insistent that I keep my columns as they are....here is my issue. I have 5 columns in which I am calculating liquidation risks of loans. A borrower may have multiple loans all with different loan numbers, yet when liquidating we take all collateral and come to one risk value on the borrower...see below
I need the preliminary risk/surplus column E to add to column C and Subtract column D if the loans are related. If not related, the tallying stops. So, risk/suplus + collater - loan amount = risk/surplus and repeat...The loans must remain in this order....any ideas of a formula to put in column E would make u my hero.
<tbody>
</tbody>
I need the preliminary risk/surplus column E to add to column C and Subtract column D if the loans are related. If not related, the tallying stops. So, risk/suplus + collater - loan amount = risk/surplus and repeat...The loans must remain in this order....any ideas of a formula to put in column E would make u my hero.
| Name | loan # | collateral $ | loan amt | risk or surplus |
| john | 1234 | 50,000 | 25,000 | 25,000 |
| john | 4567 | 100,000 | 50,000 | |
| john | 7890 | 6000 | 0.00 | |
| john | 5678 | 0.00 | 25,000 | |
| sam | 6709 | 50,000 | 25,000 | 25,000 |
<tbody>
</tbody>
