My Small World

We often tend to encounter instances while using formulae performing day to day operations where the formulae results in an error. Often, some basic users prefer manually correcting those errors and so advance users go one step ahead using a combination of "IF" & "ISERROR" functions. Even I'm one of those users who prefer using the "IF" & "ISERROR" combination until I came across the "IFERROR" function. Syntax: IFERROR(value,value_if_error) According to Microsoft, "IFERROR" evaluates  #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! type of errors. Say: Cell A1 contains a numeric value and Cell A2 contains 0 as value. You intend to perform a simple operation A1 divided by A2 (=A1/A2). In our example, the result would be #DIV/0!. Now, lets redo this simple division using "IFERROR" function. The formula can be written as "=IFERROR(A1/A2,0)" wherein the formula would asses that if the...

In Excel 2007 and 2010, there is a feature called AutoRecover that saves your open workbooks at regular intervals. In the event of a power failure or computer crash or freeze up, this can help you recover your unsaved work. This feature is turned on by default and is hardly noticeable as it happens in the background. However, you may notice AutoRecover saving files, and may even get annoyed by it, if you have one or more large workbooks open, as it could tie you up for 5 to 10 or more seconds. I often have very large workbooks open that contain only reference information (i.e. files that I'm not making changes to) and other workbooks which I don't require to be saved by the AutoRecover feature. To avoid the unnecessary saving of these files, there is an option available to turn off AutoRecover for individual workbooks. Here's how... 1) Click the File tab (2010) or the Office Button (2007); 2) Click Options (2010) or Excel Options (2007); 3) Clic...

A simple subtraction between two date values gives you the number of days between the two days. However, to actually calculate the exact number of days or number of completed months or number of completed years, Excel offers a hidden function called "DATEDIF". Syntax: =datedif(date 1, date 2,criteria), where date 1 is the start date or the older of the two dates date 2 is the end date or the recent date Criteria can be as follows:   "d" - number of completed days between the two dates   "m" - number of completed months between the two dates   "y" - number of completed years between the two dates   "yd" - number of completed days between dates ignoring the year i.e. considering them to be in the same year  "ym" - number of completed months between dates ignoring the year i.e. considering them to be in the same year  "md" - number of completed days between dates ignoring the year & month i.e. considering...

Excel 2007 and 2010 versions allow easy shortcut keys to change the case of the text in any given cell. Proper case (1st letter of every word in upper case) - shift + ctrl + p Upper case (all characters in upper case) - shift + ctrl + u Lower case (all characters in lower case) - shift + ctrl + l Isn't it handy?? Published with Blogger-droid v1.6.7