Problem With Date Column

[CARBOB]

This is the problem. I have a formula that counts the number of draws that a 3 digit number has missed since it's been drawn. I am getting a negative number for the count. I'm not sure if it's because the sheet I'm referenced too has many dates the same or if it's because the dates are in ascending order. The formula works great on other worksheets with dates in descending order. The data on the sheet "MIRRORSMATESTOTALS" is created by a macro with data from another sheet. Is there any way the formula can be altered? Thanks for any suggestions. I have posted both sheets.

Carbob

DOUBLESMIRRORSTOTALS MASTER.xls
	A	B	C	D
4	COMBO	HITS	SKIPS
5	001	37	-63
6	010	37	-22
7	100	38	-40
8	002
FILTERS

DOUBLESMIRRORSTOTALS MASTER.xls
	A	B	C	D
1	DATE	DRAW	MATE	MIRROR
2	01/01/03	147		692
3	01/01/03	559	995	004
4	01/01/03	741		296
5	01/01/03	148		693
6	01/01/03	706		251
7	01/01/03	966	699	411
MIRRORSMATESTOTALS

 

[CARBOB]

bUMP

 

[Peter_SSs]

CARBOB

It looks like your formula basically subtracts a date found in column A from the date in $A$2. Since the dates are ascending, you are subtracting a larger number from a smaller number and so it's not surprising that the result is negative.

That problem could easily be solved by changing the order of the subtraction. However, subtracting the date in $A$2 from the date the combination was last drawn isn't going to give you the answer you want as far as I can see.

What I think you want is the last date in column A minus the last date the combination was drawn. The only way I can think of to do that will make your sheet VERY slow to calculate if you have 48,165 rows as your formula suggests. Try this with a smaller range first.

I have done this on one sheet only for simplicity in testing. Formula in F2 (copied down) is:
=IF(ISNUMBER(MATCH(2,1/(B$2:B$100=B2))),LOOKUP(9.99999999999999E+307,A$2:A$100)-INDEX(A$2:A$100,MATCH(2,1/(B$2:B$100=B2))),"")
Confirmed with CTRL+SHIFT+ENTER

Mr Excel.xls
	A	B	C	D	E	F
1	Date	Draw				Skips
2	01/01/2003	123				2
3	01/01/2003	456				4
4	01/01/2003	856				1
5	02/01/2003	999				3
6	02/01/2003	654				3
7	02/01/2003	321				3
8	03/01/2003	159				2
9	03/01/2003	951				2
10	03/01/2003	123				2
11	04/01/2003	568				1
12	04/01/2003	856				1
13	04/01/2003	888				1
14	05/01/2003	702				0
15	05/01/2003	203				0
16
Skips

 

[CARBOB]

Peter,
I need the formula on the "Filters" sheet to reference the 3 digit nubers in col "A" and the dates are on the "Mirrorsmatestotals" sheet. Here's what I got. I probably have something wrong, no doubt.

Carbob

DOUBLESMIRRORSTOTALS MASTER.xls
	A	B	C	D
4	COMBO	HITS	SKIPS
5	001	37	37624
6	010	37	-22
7	100	38	-40
FILTERS

 

[Peter_SSs]

CARBOB

I am unable to access the formula you have in column C in your last post.

Yes, I understand about referencing a different sheet in your situation but thought you might be able to adapt what I gave you. I will modify it if required, but did my previous sample post give the results you would expect from my sample data?

 

[CARBOB]

Disreguard Peter works like a champ. Sometimes this "Ole" man can't see the forest for the trees. Thanks a lot.

Carbob