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

Match year then find earliest date of year (in a range) and return cell adress of date

R

Ria

Member
Hi all:

I am new to this forum and have some know knowledge of exel. Here is my question:
I have a dates in column A in format: 21/01/2014. Dates are entered weekly for same year (starting from 1995 till now, 1000 rows of data).
I want to search a year (e.g. 1996 or 2003), when year is found then find earliest OR latest date of that year and return cell adress of that date.
No VBA code please, no pivot table.

Any help woud be appreciated. If question is not clear, please let me know and will try to restate.
 
Hi Narayan:

Attached is data file. I need to pass that cell value (found date based on year search) to create dynamic chart. Chart part I can handle only need solution of first statment in this thread.

Regards,

Ria
 

Attachments

  • TEST.xls
    434.5 KB · Views: 6
Hi Ria ,

Given a year in A1 , the earliest date of that year in column A :

=INDEX(Dates,MATCH(DATEVALUE("1/1/"&$A$1),Dates,-1))

The latest date of that year in column A :

=MAX((YEAR(Dates)=$A$1)*Dates)

entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Hi Narayan:

In first part to find earliest date, you are using word: Dates, did you name column A: Dates. Did not work then I replaced word Dates with A:A and it worked fine. Thanks for this part.

Second part to find latest date does not work, even I changed Dates with A:A. It always shows 0-Jan-00.
Need more input from you.

Thanks again looking at it.

Ria
 
Narayan:

Got the idea. earliest date part is working fine.
But max/last date/latest date of the year part, for this part year could be different than the first part. Please help in this part. e.g. 1) 2009, find max/last date/latest date of year in range cell A
2) 2005, find earliest/first date of year in range cell A
Basically I will be enterting 2 years for charts e.g. start year: 2004 and end year: 2010 to get range of data to plot a chart.

Please help to find max/last date/latest date of year.

Thanks

Riaz
 
Hi Riaz,

Well I did not understood what do you mean by max date for a year. But to find first & last date use below array formulas,

First Date: =SMALL(IF(YEAR(Dates)=$A$1,Dates),1) ... Confirm with Ctrl+Shift+Year.

Last Date: =LARGE(IF(YEAR(Dates)=$A$1,Dates),1) ... Confirm with Ctrl+Shift+Year.

Here Cell A1 Has year and Date is a named range define by Narayan Sir , with the ref. =MyTest!$A$4:$A$1002

See if this meets your requirement.

Regards,
 
Hi Riaz ,

If you are entering two years , you need two cells ; use the two formulae with the two cells ; the second formula will reference the second cell instead of A1.

Narayan
 
Hey guys:
Your response is quick and amazing. Look like this forum runs 24x7. I really like and thanks a lot for help. I spent almost 2 weeks (about 8 hours daily average) surfing internet to find if there is any example for my problem and was tired. Finally, hit Chandoo forum and got solution. Again thanks.

Now another question:
1. can we use these formulae without defining/creating date range name (e.g. can we use A:A for date range instead naming it as: Dates). I tried with A:A and it worked fine with: LARGE(IF(YEAR($A:$A)=$BJ$4,$A:$A),1)),$A:$A,0), however it does not work with: SMALL(IF(YEAR(DateRange)=$BJ$5,DateRange),1).

2. In case there is no choice and we have to use only defined range name, then does this range grow/expand when we enter new data, if not then how to handle.

Regards

Ria
 
Hi Riaz ,

A named range is static if the start point and end point are defined by absolute cell references ; in your example , if we define the named range Dates as referring to : =MyTest!$A$4:$A$1002 , it is a static named range.

As and when you add a date in cell A1003 , you will have to go back to the Name Manager , and change the definition of Dates to include this new date , by : =MyTest!$A$4:$A$1003.

A named range is dynamic if the definition is such that newly added data is automatically included , without your having to manually do it. This can be done , in your case , by changing the definition as follows :

=MyTest!$A$4:INDEX(MyTest!$A:$A,COUNTA(MyTest!$A:$A))

With this definition , if you add a new date in cell A1003 , you will see that the range has automatically extended to A1003 , to include the newly added date.

Of course , in your file , you have put the most recent data in row 2 , which makes it more complex ; can you explain how you will add new data ?
-----------------------------------------------------------------------------------------------------------------------

Your formula : =SMALL(IF(YEAR(DateRange)=$BJ$5,DateRange),1) will not work with entire column references , since blank cells will be treated as having 0 , and the above formula will return 0 as the smallest value. The additional problem in your case is that you have text headers in rows 2 and 3 ; taking the YEAR function on this text will generate the #VALUE! error.

To take care of blanks and text , you will have to add an additional IF function to exclude them , as in :

=SMALL(IF(ISNUMBER($A:$A),IF(YEAR($A:$A)=$BJ$5,$A:$A)),1)

However , since entire column references add to the recalculation time , it is always better to avoid them.

Narayan
 
Narayan, thanks again.
To make date range dynamic, follwoing formula worked fine:
MyTest!$A$4:INDEX(MyTest!$A:$A,COUNTA(MyTest!$A:$A))
However, following did not work and still gives error:
SMALL(IF(ISNUMBER($A:$A),IF(YEAR($A:$A)=$BJ$5,$A:$A)),1)

Adding new data, we get data in this format (latest on top) and we have separate spreadsheet where we insert a line on top and paste new data in that new line. And it becomes our master data file. Then copy and past into this spreadshee in cell A4 and use it. This is how data grows/expands.

Again big thanks.

Riaz
 
Hi Riaz ,

I forgot to mention that the second formula is an array formula , and needs to be entered using CTRL SHIFT ENTER. Did you enter it like this ?

Narayan
 
Hi Narayan:
I tried second formula with CTRL SHIFT ENTER and it returns ZERO.
Any thought?

Riaz
 
Hello Riaz,

You could use DMIN & DMAX, which is faster than array formulas. See attached file.
 

Attachments

  • TEST.xls
    443 KB · Views: 7
Hi Narayan:

File uploaded. First formula is in cell BM4 that works fine. Second formula is in BM6 which does not work and returns zero. If both of these formulas work then I will put them in cell BK4 & BK 5.

Another question related to charts but not important though. If any of you guys are expert in charts.
I enter start and end years in cell BJ4 & BJ5.
Cell BK4 will find the last date of that year and will return line number (from column A) and
cell BK5 will find the first date of that year and will return the line number (from column A)
Then based on these 2 dates/line numbers, chart is plotted. Here is the problem with chart:
It should plot chart based on these 2 line numbers/dates but chart starts from few lines earlier dates/lines. For example see in cell BK5 is line 23 which is date 7-Jan-2014 in column A. But chart start date is 10-Dec-2013 which is wrong. If anyone can handle it. That is the whole story of creating these formulas.

Can not upload file, file is too big mesage appears and tried to delete few charts but still can not upload.

Thanks,

Riaz
 
Hi Riaz ,

You cannot upload files to this forum if their size is more than 1 MB.

In such a case , please use any public file-sharing website , such as RapidShare , DropBox , GoogleDocs , SkyDrive to upload your file , give others permission to access and download your file , and then post that access link here in this thread.

Narayan
 
Narayan:
Deleted lots of data and now uploaded file but will give you clue what I am asking for.

Riaz
 

Attachments

  • COT_Test.xls
    333.5 KB · Views: 2
Hi Riaz ,

See this file.

I have changed the definitions of two named ranges Date and DateRange slightly. In case you have any doubts about these changes , let me know.

Narayan
 

Attachments

  • COT_Test.xlsx
    238.6 KB · Views: 5
Hi Narayan:

Thanks a lot, it works fine.
Cell BK4 & BK5 should display row number relavant to start date/end date of the year (e.g. for 2014/2014: 20-May-14 & 7-Jan-14, actual row numbers in column A are 4 & 23). Currently it is displaying 1 & 20. Is there anyway we can match them to actual row number e.g. 4 & 23.
 
Hi Riaz ,

See your file now. I have changed the definition of the named range Date.

Narayan
 

Attachments

  • COT_Test.xlsx
    237.8 KB · Views: 7
Narayan:
Another error, if you put curser on chart green or blue line, it is only reading data till 6-May-14, after that 13-5-14 & 20-5-14 it is reading zero and chart line touching zero. However last 2 dates have valid data in it. Need to look this one too.

Riaz
 
Hi Riaz ,

I think you should start learning.

Go through the earlier definition of the named range Date , see the present definition , and then make the same modification in the definition of the named ranges NetCom and NetNonCom ; in fact where ever you have used BK4 and BK5 , you will have to make the same changes , since when you use values in BK4 and BK5 with the OFFSET function , since the starting point is row 1 , you need to subtract 1 from the offset value i.e. BK4. Thus , you have two ways of defining these named ranges :

1. =OFFSET(MINDEX!$I$1,MINDEX!$BK$4-1,0,MINDEX!$BK$5-MINDEX!$BK$4+1)

2. =INDEX(MINDEX!$I:$I,MINDEX!$BK$4):INDEX(MINDEX!$I:$I,MINDEX!$BK$5)

Narayan
 
Narayan:
I figured out why it is not reading data for lastest 2 dates of 2014. I can fix that.
One last thing is:
You used BM4 & BM5 to calculate BK4 & BK5. Do not use BM4 & BM5 and BL4 & BL5, I will delete them later once I have tested it fully. So, please calculate BK4 & BK5 with out BL & BM cells.

Appreciate your help.

Riaz
 
You must log in or register to reply here.
Back
Top