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

Find nth Column Number for first instance of data occurance

Hi,


I have a data of Customers and their Monthly Invoicing details in MOM format as shown below. Now, I want to find their first month when they invoiced and also calculate their first six months of total business.

Data is as below for reference:-


Customer Jan/10 Feb/10 Mar/10 Apr/10 May/10 Jun/10 Jul/10

ABC 70.0 61.6

XYZ 63.9 64.1 79.9

ABC1 51.0 45.2

XYZ1 29.3


Thnaks in Advance for Help.
 
Hi Shekhar ,


For the first occurrence , you can use the following formula , entered as an array formula ( using CTRL SHIFT ENTER ) :


=MATCH(TRUE,Data_Range>0,0)


For the 6-month total , use the following formula :


=SUM(OFFSET(Data_Range,,Match_value-1,,6))


where Data_Range is the data range e.g. B2:M2 , and Match_value is the month value obtained using the first formula.


Narayan
 
Back
Top