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.

Align Sales by Launch Month

Discussion in 'Ask an Excel Question' started by raiyanimv, Jan 11, 2017.

  1. raiyanimv

    raiyanimv New Member

    Messages:
    6
    Hello everyone,

    I have thousands of products launched at different time points. Want to align the sales from the month of their launch. Have attached file here as an example. Need a formula to this in Excel 2010

    thanks

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    2,654
    You can do something like attached.

    Note:
    1. Launch column in first table should always be in 3 letter format for months.
    2. IFERROR(,0) can be changed to IFERROR(,"") if you want blank instead of 0 returned.

    Edit: You can also simplify things by having same date in Launch column as header date format.

    Attached Files:

    Last edited: Jan 11, 2017
    raiyanimv likes this.
  3. raiyanimv

    raiyanimv New Member

    Messages:
    6
    Hi Chihiro,

    Thanks a lot for quick reply.

    Sorry i was not clear in my request to not to consider column C with month information for calculation. Is it possible to get the alignment without keeping column C info in the formula?

    My actual data has spans over more than 2 years.

    Other way is to find out month and year of product launch and then align it. I am attaching another version which you can help me with.

    Thanks again,

    Attached Files:

  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    2,654
    Here. Another version.

    Column C contains formula to find first non-blank cell in row (returns column index in range).

    There's 2 sample tables.

    No helper column: more resource intensive array function.

    With Helper: Simple non-array formula that take advantage of helper column calculation.

    Attached Files:

    raiyanimv likes this.
  5. raiyanimv

    raiyanimv New Member

    Messages:
    6
    Thanks a lot Chihiro, I used the option without helper and it worked perfectly. Since i had to avoid '0' and '#REF' error, I created a separate table using IF and IFERROR function.

    This was very helpful....

Share This Page