• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding value against particular cell against 2 conditions

R

Rushikesh Ambekar

New Member
Hi all,

I am preparing my own spreadsheet to track the Mutual funds folio. I have come across a situation where I am unable to find the solution. I will try to explain the problem here. Please refer the attached screenshot.

Now I want excel to automatically find a NAV from column F in following 2 conditions:
1) If column D has a "Sell" value in any of the cells. In this case it should be weight average i.e. (((F32*G32)+(F40*G40))/(G32+G40)) or
2) It should pick a latest NAV against last entered date. If we eliminate "Sell" from column D then it would be F39.

Please help.

Rushi.
 

Attachments

  • Untitled.png
    Untitled.png
    47 KB · Views: 25
Hi Rushi, can you upload a sample file please. Screenshots are not enabling us to try stuff. With a workbook and sample data the thread is easier to understand.
 
GraH - Guido,

Attached file as you have asked. I have mentioned the problem in the initial message. The Yellow cell should return to the correct value depending on the conditions explained above.

Hoping this get resolved. Thanks in advance.
 

Attachments

  • Testsheet.xlsx
    9.8 KB · Views: 5
Thx for the file. However it has not made things crystal clear for me. Sorry for that.
Is the weighted average the first sell nav value * units + second sell nav value * units / sum of units of first and second sell?
What if there is only 1 sell left then? Or when there are 3 sell values? It is the last one that should be calculated?
 
Hi,
Even if it is 1 sell, 3 sell or may be more it should calculate weighted average. Automatically in case of 1 sell it will be sell nav.

Gormula need to find no of sell iteration in column D and take weighed average of NAV and units alloted against the sell value.

Also the same formula should give latest NAV in case there is no sell in column D. I have figured the formula for this condition and that is as follows:
VLOOKUP(Sell, Dx:Gy, 3,0), where Dx is start cell of data table and Gy is end of the same.

I need your help in finding formula for 1st condition and also in putting all the pieces together.
I hope I have clear the doubt. And thank you very much for taking extra effort to help me out.

Rushi.
 
Hi, I'm sorry to say I don't have much time for the moment to really dive into it. I did look at it after reading your reply on my questions - appreciated - but I don't seem to get it. Otherwise I would have offered already some help.
You called others to the rescue, let's see their responds.
 
I'm afraid I'm confused as well. Can you try explaining the whole process that formulas should be doing? I.e.,

"If there's at least one "sell" value in col D, then take cells F4:F28 and multiply by 5"
 
@GraH - Guido thank you for your time and efforts. I still look forward for your kind help.

@Luke M
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 be consists of data entry as every month I will be putting some money as investment and that is named as "SIP". And at some point of time I will withdraw that money which I labeled as "Sell". If you have a look into the test sheet attached in a comment on 15th April i.e. on Sunday.
This 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. So there are 2 cases for checking valuation on the day and those are:
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 (formulae I tried to explain in the comment on 16th April).
2. If column D doesn't have "Sell" entry, the formula should give a NAV value entered against latest date.

I hope I am being clear on the questions you have asked. Let me know if you need any more information on this.
 
You must log in or register to reply here.
Back
Top