Finding value against specific cell against 2 conditions

Hello Excel mates,
I am trying to build a portfolio tracking sheet customized specifically for tracking mutual fund. Where I came across a problem that I will explain again in detail here.

Sheet will consist of data entries which are:
1. Every month there will be some money invested and that I named as "SIP".
2. And at some point of time I will withdraw that money which I labelled as "Sell".
Please have a look into the test sheet attached here.

The data here contains column D as sell or SIP/Sell transactions, Column E is amount invested or withdrawn, column F has Net asset value (NAV) and column G contains Units allotted.

Now I want to write a formula to auto calculate NAV in Cell H2 (Yellow highlighted cell) depending on following 2 cases:
1. If Column D has one or more "Sell" entries, then the NAV should be weighed average of no of units and NAV against sell rows. Here as per the data entered it will be (((F35*G35)+(F38*G38))/(G35+G38)) i.e. 21.8939 (answer). Weighted average calculated in cell J15 just for a reference.
Or the second case will be:
2. If column D doesn't have "Sell" entry, the formula should give a NAV value entered against latest date SIP investment / Div. Re-invest. In this case it will F37 i.e. 23.07 (answer).

I hope I am being clear on the question that I want to ask. Let me know if you need any more information on this.

Rushi.

Why F35 and F38 ????

Sambas has given you the formula.

Re my question: I should have looked more carefully at the data!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks nflsales,
I have tested formula and it perfectly works.

John Topley,
I have changed the status of the thread.