My Pleasure, By Pressing CTRL+SHIFT+ENTER, we create an array formula. And one of the restrictions of the array formula is, it can not work on an entire row, or column. I would urge you to analyze this formula and any other formula with Tools->Formula Auditing->Evaluate Formula. And you will see the difference as to how the arguments are evaluated.
If want to know more about array formulas, look in excel documentation or mr.excel.com or cpearson.com. Regards, Ajit On Sun, Oct 12, 2008 at 9:35 AM, Arjunm1984 <[EMAIL PROTECTED]> wrote: > > Hello Ajit, > > I was just going through this formula, I have a couple of questions on > the solution that you have given. Hope you can help me on this. > > 1) Why is there a difference if we just press ENTER in the below > formula. Why CTRL + SHIFT + ENTER? > > 2) Why doesn't it give the answer if we give the range as B:B in the > formula? > > Thanks, > Arjun > On Oct 10, 7:17 pm, "AJIT NAVRE" <[EMAIL PROTECTED]> wrote: > > Set up the table (2nd table) with headers (required Period Slabs) and use > > the following formulas, > > Less Than 6 months Formula > > > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") < 6,1)) > > > > 6 Months To 1 Year > > > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 6,IF(DATEDIF(B2:B7,TODAY(),"m") <= > > 12,1))) > > > > 1 year To 2 Years > > > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 13,IF(DATEDIF(B2:B7,TODAY(),"m") <= > > 24,1))) > > > > 2 Years To 3 Years > > > > =SUM(IF(DATEDIF(B2:B7,TODAY(),"m") >= 25,IF(DATEDIF(B2:B7,TODAY(),"m") <= > > 35,1))) > > > > Greater Than 3 Years > > > > =COUNT(IF(DATEDIF(B2:B7,TODAY(),"m") >= 37,1)) > > > > Remember: > > On Each formula, after entering the last closing paranthesis ")", end the > > formula by pressing CTRL+SHIFT+ENTER, and not only ENTER. > > > > The Range B2:B7 should be replaced with the appropriate Joining Date > Range > > Address In Your Data. Make it a point not to enter the entire column as > in > > B:B. > > > > This solution assumes that, you want this tenure break-up only for a > limited > > slab (6m, 1Y, 2Y, > 3Y). If you want to calculate tenure for an infinite > > slab, then you probably need a macro. > > > > Let me know if this was what you were looking for. > > > > Regards, > > > > Ajit > > > > On Thu, Oct 9, 2008 at 7:28 PM, Krishna Kishore <[EMAIL PROTECTED] > >wrote: > > > > > > > > > > > > > Hello All > > > > > I am looking for a help to create a file in excel which can capture the > > > exact tenure of people in an organization. Your inputs can help me in > > > achieving this. > > > > > I am looking for a report in which excel can segregate the staff in the > > > following categories. > > > > > Name > > > > > Date of Joining > > > > > Rahul > > > > > 19-12-2004 > > > > > Lakshman > > > > > 22-11-2005 > > > > > Azhar > > > > > 01-01-2008 > > > > > Vinod > > > > > 01-08-2008 > > > > > Through this the end result should be out in this format > > > > > Total > > > > > Less than 6 months > > > > > 6 months to 1 year > > > > > 1 year to 2 years > > > > > 2 years to 3 years > > > > > 4 > > > > > 1 > > > > > 1 > > > > > 1 > > > > > 1 > > > > > I would be thankful to all in advance. > > > > > <http://smsxite.com/picdetail.php?catId=43&tid=39> > > > > > Krishna Kishore > > > [image: Friendship SMS] < > http://smsxite.com/picdetail.php?catId=43&tid=39> > > > > -- > > Thank You, > > > > Ajit Navre > > > > image001.jpg > > 23KViewDownload- Hide quoted text - > > > > - Show quoted text - > > > > -- Thank You, Ajit Navre --~--~---------~--~----~------------~-------~--~----~ Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To post to this group, send email to excel-macros@googlegroups.com For more options, visit this group at http://groups.google.com/group/excel-macros?hl=en Visit & Join Our Orkut Community at http://www.orkut.com/Community.aspx?cmm=22913620 Visit the blog to download Excel tutorials at http://www.excel-macros.blogspot.com To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com To see the Daily Excel Tips, Go to: http://exceldailytip.blogspot.com -~----------~----~----~----~------~----~------~--~---
