1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by Rushikesh Ambekar, Apr 15, 2018.

  1. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    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.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    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.
  3. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    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.

    Attached Files:

  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    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?
    Rushikesh Ambekar likes this.
  5. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    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.
  6. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    Experts at this forum, Please help.

    I am waiting to get this resolved. It will be great help to get some experts help to complete this.
  7. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    Last edited: Apr 18, 2018
  8. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    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.
  9. Luke M

    Luke M Excel Ninja

    Messages:
    9,350
    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"
  10. Rushikesh Ambekar

    Rushikesh Ambekar New Member

    Messages:
    8
    @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.

Share This Page