+ Reply to Thread
Results 1 to 6 of 6

Complex What If Formula

  1. #1
    Registered User
    Join Date
    11-09-2018
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    31

    Complex What If Formula

    Hello all,

    I am trying to figure out a formula for this situation. The sample I provided here is just for two accounts with a few months.

    My formula would be need to follow these criterias:
    1) Need to be same account (column A)
    2) Previous MV (i.e. Column D3) - Current MV (i.e. Column C2)

    The result I want is in Column E but you can see it is not working because it is not using the same account at certain row when a new account starts (i.e. Row 6). My actual data are thousand of accounts and 5 years (monthly) of rows. Thank you in advance for helping with my question/challenge.
    Attached Files Attached Files
    Last edited by lhp2019; 06-02-2019 at 04:12 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex What If Formula

    Use this formula in E2:

    =IF(COUNTIF(A$2:A2,A2)=1,"",D2-C1)

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-09-2018
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    31

    Re: Complex What If Formula

    Pete,

    Thank you this formula works for when account changes. I found an issue with this particular formula D2-C1 if there are months that are skip. For example below, if the month went from February 2012 to May 2012, the formula will not work correctly because it is comparing May 2012 to February 2012. So I really want the formula to subtract current month (May 2012) from previous month (April 2012). If the previous month is not available then return blank/null. I have to think about this process and get back to you. Thank you!

    Account Date Current MV Previous MV
    12345 1/1/2012 62034.57 59881.97
    12345 2/1/2012 64169.59 62034.57
    12345 5/1/2012 61407.86 64142.05
    12345 6/1/2012 62927.77 61407.86

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Complex What If Formula

    Please try at E2
    =IF((A1=A2)*(DATEDIF(N(B1),B2,"m")=1),D2-C1,"")

  5. #5
    Registered User
    Join Date
    11-09-2018
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    31

    Re: Complex What If Formula

    I am sorry Bo_Ry for the late response on your suggestion as I was away. This formula works perfect. Thank you again!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Complex What If Formula

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Complex If/Then Formula
    By willhh3 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2018, 01:37 PM
  2. [SOLVED] Complex formula getting moer complex
    By Trebor777 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2017, 01:39 PM
  3. too complex formula
    By Bab1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-08-2014, 08:29 AM
  4. [SOLVED] Use an existing cell formula for creating a new more complex formula
    By dubcap01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2014, 07:41 AM
  5. Replies: 4
    Last Post: 02-03-2014, 12:47 AM
  6. Formula too complex
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-13-2011, 09:36 PM
  7. Complex Sum formula
    By hartski in forum Excel General
    Replies: 2
    Last Post: 11-19-2008, 12:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1