determine if a customer has not paid in x months

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
i have a spreadsheet with customers in column a and the associated monthly revenue from each customer in the next 25 columns.

I want to be determine if there are 0's in the x most recent months.
where x is any number.

so if i want to know if we recieved no revenue from a customer in the last 1 months it would be

assume b2:z2 are monthly dates 1/1/2007, 2/1/2007, etc...
=if(hlookup(match(now(),b2:z2,1),b2:z2,2,0)<=0,"not an active customer","active customer")

i just dont know how to do this for more than 1 month.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Suppose you have the number of months of interest in E5. Then, use the formula
=COUNTIF(OFFSET(B2,1,MATCH(NOW(),B2:N2,1)-(E5),1,E5),0)=E5
where B2:N2 represents the first of each month and the payments are in row 3.
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
thanks... if i am understanding the formula correctly, the formula returns true if the line does not have revenue within the number of months in e5.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top