Problem With Date Column

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,866
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
ABCD
4COMBOHITSSKIPS
500137-63
601037-22
710038-40
8002
FILTERS
DOUBLESMIRRORSTOTALS MASTER.xls
ABCD
1DATEDRAWMATEMIRROR
201/01/03147692
301/01/03559995004
401/01/03741296
501/01/03148693
601/01/03706251
701/01/03966699411
MIRRORSMATESTOTALS
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,356
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DateDrawSkips
201/01/20031232
301/01/20034564
401/01/20038561
502/01/20039993
602/01/20036543
702/01/20033213
803/01/20031592
903/01/20039512
1003/01/20031232
1104/01/20035681
1204/01/20038561
1304/01/20038881
1405/01/20037020
1505/01/20032030
16
Skips
 

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,866
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
ABCD
4COMBOHITSSKIPS
50013737624
601037-22
710038-40
FILTERS
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,356
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 6, 2005
Messages
1,866
Disreguard Peter works like a champ. Sometimes this "Ole" man can't see the forest for the trees. Thanks a lot.

Carbob
 
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,164,052
Messages
5,835,144
Members
430,342
Latest member
Sailingexcel

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