Sunday, December 11, 2011

IFERROR funtion

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 result of A1/A2 is an error the formula should put in 0 instead of the error.

This is indeed very helpful function which can be used with ease to perform various operations in an Excel sheet.

Monday, August 29, 2011

Turn Off AutoRecover For Individual Workbooks

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) Click the Save option on the left;

4) In the 'AutoRecover exceptions for' section, check the 'Disable AutoRecover for this workbook only' option.

(adapted from "excel addict")

Friday, August 19, 2011

"DATEDIF" function to calculate the number of days/months/years between dates

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 them to be in the same year and same month

Therefore if I want to calculate my age, I can write the formula as:

Date 1: 1-Jan-1980 (H4 cell)
Date 2: 19-Aug-2011 or today (I4 cell)
Formula: =DATEDIF(H4,I4,"y")&" years "&DATEDIF(H4,I4,"ym")&" months and "&DATEDIF(H4,I4,"md")&" days"
Result: 31 years 7 months and 18 days


Caution:
  • Date 1 ought to be lesser than or older than Date 2
  • of course, both dates should be valid date formats
Try it... its interesting!!

Thanks Charan for reminding me on this function, which I had stopped using.

Sunday, May 29, 2011

Lost in the vicinity...


TIt has been a while since I've blogged...not because I was too busy but reasons beyond thought!!

I'm in search of myself...!!

Its not that my search is over but its time for me to get back to my usual action!

My neighbor was chatting with me while I was standing at the park nearby... He asked me what I'm looking for in my life partner. I usually dont entertain such queries but the person was quite old so I could not avoid. I simply answered - "She needs to be literate and self driven, thats my only requirement".

He smiled at me and said - "Thats a smart answer and I wish you luck"

The reaction was not anticipated as I expected him to be like all people of his age offering me help to find my better half. I gave him a confused smile and walked back home.

Lying on my bed, I was thinking about the old man's reaction. I realized that we anticipate based on things that get repeated frequently in our lives and forget that every moment is not the same.

This small incident today has lifted my spirits to think different yet again...n here I'm back on my blogging spree!!

Now the hunt for myself has gone to a new platform all together!!
Published with Blogger-droid v1.6.9

Saturday, March 12, 2011

Changing case of text in a cell

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

Wednesday, January 26, 2011

Halla Bol

A sunny welcome to the 62nd Republic Day. Woke up to enjoy the holiday and tuned in to the television. Surfing through the channels, I decided to watch "Halla Bol".

The screening of this movie on this is so so apt that describing the aptness will call for words more than those available in the dictionary. Being republic just for the sake or do we really understand the meaning of being a republic country.

A few weeks back, I was walking back home after some shopping (groceries etc), when I noticed a big crowd standing around something. There were flashes of camera from the inside end of the crowd. Too hard to guess what was going on inside. Holding on to my curiosity and the 6 kg bag on one hand, I made way to the crowd. Taking advantage of my short height, was able to reach to the epicentre of the event... Was expecting something exciting but surprisingly it was not exciting but shocking. A young lady (in her late 20s) and a small kid (about 3 yrs) were lying in a pool of blood!! What were the people who crowded upto??? I could just hear discussion on the probabilities of the accident and the chances of the victims' survival.... On the othe hand, our dear media crew was busy covering the tragic event than taking any action as responsible representatives of our society....

The first thought within me was to just walk away thinking world is a bad place to live in... So I turned around. But then there was a pricking sensation within which held me back making me think that if I walk away, I too add to the bad-ness of this world.... I turned back, walked towards the pool of blood but realised it was too late for me to help them.... With a guilt concious, I called 100 (anonymously) and informed about the accident. Police reached the spot in 10 mins, cleared the crowd and took away the bodies!!

All the while I had been thinking, could anyone have saved the mother-child....? Our media network is so strong that they are able to reach at all incident spots in a flash even before the police can reach.... So.... They are the ones who can lead the way with their thinking power, activness and intelligence. Are stories more important than saving a life..??

Even, we the citizens need to move with equal responsibility and start leading and stop being lead!!!

Together we the people can make INDIA a true REPUBLIC country where we know our responsibilities towards ourselves and our society....!

So India - Happy Republic Day....

Wake up and Hall Bol....!
Published with Blogger-droid v1.6.6

Sunday, January 16, 2011

Life on Android

Its been over a week since I last connected my system to the net...! Reason: my new android phone! Social networking has gone so accessible that I do not need a system to get online...

Be it getting online on skype or be it blogging.... Its right there on my palm! Isn't the world getting smaller n smaller??

There was time when I used to go long way to a friends place to access internet to view the results of my board exams.... Thats a decade old story:-)

Today, be it locating a place on the map or exploring info about a topic on google or wikipedia its all a flick away... Moreover, with google android, u need even load an app to read a wall post on fb or check mails on gmail, one is connected from the word go!

Hats off to this techo revolution.... Looking eagerly forward for a decade down the lane, when info will be available on a gadget to the size of my thumb....!

I'm loving it!
Published with Blogger-droid v1.6.5