Align Sales by Launch Month

[raiyanimv]

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

 

[Chihiro]

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.

 

[raiyanimv]

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,

 

[Chihiro]

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.

 

[raiyanimv]

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