If then as a VB function

[TGroulx]

What I am trying to do is create a tool to generate stat data using VB. I have so many records that using formulas and filters is killing me.

Here is a sample Table
Col A - Unique ID
Col B - Date 1
Col C - Date 2

I want to run a function that returns a stat (ex: average) for all records where Date 2 falls within a timeframe.

Example: Calculate Average of (Date 2 - Date 1) for records that -2 is in the month of October.

Any Help here?

 

[stanleydgromjr]

TGroulx,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Colo's HTML Maker.
http://www.puremis.net/excel/downloads.shtml

Instructions for using "Colo's HTML Maker":
http://www.mrexcel.com/forum/showthread.php?t=89356


Or, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Richard Schollar]

How many records are you talking? By the sounds of it that should be a fairly straightforward array formula:

=AVERAGE(IF(MONTH(Date2Range)=10,Date2Range - Date1Range))

confirmed with Ctrl+Shift+Enter.

 

[TGroulx]

How many records are you talking? By the sounds of it that should be a fairly straightforward array formula:

=AVERAGE(IF(MONTH(Date2Range)=10,Date2Range - Date1Range))

confirmed with Ctrl+Shift+Enter.

I could not get that to work, my sheet is 9000 Records by 76 Fields.

Was hoping to use VB with a user form so that I can make it intuitive for others to use. I have not used VB much beyond manipulating others macros, and simple macro records.

 

[TGroulx]

Screen Shot:

Sample.xls
	A	B	C	D	E	F	G	H	I	J	K
1	NCNumber	Status	DateofEvent	Year-MonthofEvent	Date-1Complete	Date-2Complete	DateMat'lReleasedfromQRB	Date-4Complete,System	DateMat'lCheckedoutofMQ	Date-4Complete,Component	Date-3Complete,NonProduct
2	084NCRP05000	Closed	10/03/08	08-10	10/03/08	10/06/08		10/06/08
3	084NCRP05001	Closed	10/03/08	08-10	10/03/08	10/06/08		10/22/08
4	084NCRP05002	Closed	10/03/08	08-10	10/03/08	10/06/08		11/19/08
5	084NCRP05003	Closed	10/03/08	08-10	10/03/08	10/06/08		10/13/08
6
7
8
9
10	WanttoCreateThis:
11		Average-2completion
12	October	=if(Month(E:E)=10,average(e:e-c:c),donotcount)
13	November	=if(Month(E:E)=11,average(e:e-c:c),donotcount)
14	December	=if(Month(E:E)=12,average(e:e-c:c),donotcount)
15	Etc.
16
Sample Dataset