Tech2 Touch

Author: Aniruddh

  • Excel Formula to Write Date Format In Excel

    In excel we can use a date in different formats according to our wish by clicking on format cells and selecting the required Date format. This works on most of scenarios. But using formula to set the format of date comes handy at times.  So in this Excel tips and tricks guide we will learn to use Excel formula which converts date into desired format.

    Excel Formula to Convert Date in desired Format

    This way of formatting comes very handy when you are using nested function or output of any function which is date. For example you are creating template and sharing with other. They might not be knowing the formatting. So when extend the formula to some other row or column, they will not get the date in desired format. So if you embed the date format in the excel function itself, then it will be more easier. For formating the date in excel using function, text worksheet function is useful. Example is given for such scenario is given in the end of this post.

    Syntax of Text worksheet function

    =text(Date, Date Format)

    Example:
    Let us say, the date is in cell A1, then the formula will be
    =TEXT(A1,”dd-mmm-yyyy”) which returns date in 24-Apr-2013
    =TEXT(A1,”dd-mm-yyyy”) returns date in 24-04-2013 format
    =TEXT(A1,”dd-mmmm-yyyy”)return date as 24-April-2013
    =TEXT(A1,”yyyy”) returns 2013
    =TEXT(A1,”mmmm”) returns April
    Hope you got the concept of using Excel formula to get Date format.  This Excel formula to format date is very useful when you are using date inside or along with another Formula. For example, let us say in cell A1 we have “todays date” and in cell B1 we have 24-April-2013. When we concatenate these two cells, the result shows  todays date41388. To make date to appear in correct formar we need to enter the formula as mentioned below.
    =A1&TEXT(B1,”dd-mmm-yyyy”) which returns todays date24-Apr-2013.
    Please let me know if you need further details on the same.

  • Excel Vlookup Formula with IF Function-Condition Based vlookup

    I have already written on basic vlookup function and conditional vlookup function. In this Excel tutor, i will show you how you can use vlookup function along with IF function. Recently i came across a situation where i need to use different vlookup formula when there is a different scenarios. So i thought even this one will help you also. This excel tips guide will help you on how to perform different range conditional vlookup in excel.

    Using VLOOKUP formula with Nested IF function

    Actually, i came across this formula, while i was working on a annual salary revision data for a company. There it had all the countries employees with different grades and bands in one sheet. I have been provided with a grid which has increment amount for each grade, grade upgrade and promotion for different countries.  The employee data has country details, previous grade, current grade and the band.  One way is to split the employee data into three different sheets and performing. But i had a restriction on that. I should do for all countries in one sheet. Below is the steps I have done.
    Nested IF and Vlookup Formula Example
    1. I have created one more table for the Proposed Salary increment Grid. A unique id created by concatenating the previous grade, current grade and Band with the help of separating operator !. Please refer the below excel sheet. From A1: F12 contains original data table. From H2: K 12, the calculated salary increments for the different scenarios.
    2. Then created the unique code in the employee sheet too with the same logic of concatenating  previous grade, current grade and Band with the help of separating operator !. In the example, employee data is in A15 to G21. Added unique code in Column G.
    3. Now, in the increment amount field column F17, inserted the vlookup formula with Nested If function as mentioned below.

    =IF(B17=”India”,VLOOKUP(G17,$H$4:$I$12,2,FALSE),IF(B17=”US”,VLOOKUP(G17,$H$4:$J$12,3,FALSE),VLOOKUP(G17,$H$4:$K$12,4,FALSE)))

    4. Copied the same  formula to all the employees. The employee sheet has the increment amount for all the employees based on their grade upgradation or promotion and their countries.

    The above logic is simple, Tested the condition and while the condition is met, executed one set of vlookup formula while that condition is not met, tested one more criteria and ran different set of vlookup formula and so on.  By default you can Nested if for 7 times. For using IF formula more than 7 times, you need to take help of Excel VBA custom function.

    Share your thoughts on the same. If you have any better solution for this or if you need any clarification on this, share it in the comment section below.

  • Sony Xperia Z Review- Disadvantages and Advantages

    Sony Xperia Z is Android 4.1 Jelly Bean running smartphone with 5 inch Full HD display screen. The device is powered by quad core 1.5 GHz Qualcomm Snapdragon S4 Pro processor with 2GB RAM and Adreno 320 GPU. The price of Xperia Z is Rs 34,990.
    Sony Xperia Smartphones

    Sony Xperia Z sports 5 inch TFT capacitive touch screen with a screen resolution of 1080 x 1920 pixels resulting in a pixel density of 441 ppi and has 16M color depth. The display is shatterproof and scratch resistant glass.  The internal storage of the device is 16GB and supports microSD card upto 64GB.

    Sony Xperia Z has 13.1MP auto focus primary camera with LED flash and 2.2MP front facing camera. Both front and back cameras can record 1080p HD videos at 30fps.

    For connectivity, Xperia Z supports Wi-Fi, 2G, 3G, Bluetooth 4.0,NFC and microUSB 2.0. This Xperia smartphone can be used as modem to connect to internet from PC with its tethering capability. It supports both USB and WiFi tethering. The Xperia Z has Non-removable Li-Ion 2330 mAh battery which is promises to provide upto 11 hours of talk time and 550 hours of standby time.

    Advantages and Disadvantages of Xperia Z

    Pros of Xperia Z

    • IP57 certified water and dust resistant build
    • Appealing distinctive design
    • Great Camera performance
    • Memory expansion option
    • Powerful performance with quad core processor

    Cons of Xperia Z

    • Non removable battery
    • The viewing angles of the display screen is not very great
    • Low light camera performance is not very great
    • Few Firmware Glitches
    • No out of box Android 4.2 Jelly Bean OS

    Sony Xperia Z Specification

    • Chipset:Qualcomm MDM9215M / APQ8064
    • Processor:Quad-core 1.5 GHz Krait
    • GPU:Adreno 320
    • RAM: 2GB
    • Dimensions:139 x 71 x 7.9 mm
    • Weight: 146grams
    • Primary Camera:13.1MP auto focus with LED flash
    • Camera Features:Geo-tagging, touch focus, face detection, image stabilization, HDR, sweep panorama
    • Secondary Camera: 2.2 Megapixel,1080p@30fps
    • Display: 5-inch TFT capacitive touch screen
    • Display Screen resolution:1080 x 1920 pixels(~441 ppi pixel density).
    • Display features:Shatter proof and scratch-resistant glass and Sony Mobile BRAVIA Engine 2
    • Internal Storage: 16GB
    • Expandable memory: Yes. Supports microSD card upto 32GB
    • Connectivity:Wi-Fi, 2G, 3G, Bluetooth 4.0,NFC and microUSB 2.0
    • Battery:Non-removable Li-Ion 2330 mAh battery
    • Stand-by time:Up to 550 h (2G) / Up to 530 h (3G)
    • Talk time:Up to 11 h (2G) / Up to 14 h (3G)

  • Using VLOOKUP Formula in Excel effectively using MATCH Worksheet function

    In my earlier Excel tutorial post, we have learned how to use vlookup function. In this post, I will show you simple tweak through which you can make VLOOKUP function more effective. This will be useful for you when you want to map huge data.

    For example you have base table with more than 20 or 30 columns. From that, you need to fetch selected 5 or 6 columns in different part of the sheet or in different sheet itself. For doing this we need to use MATCH worksheet function along with VLOOKUP function.

    Using Excel VLOOKUP with Match Formula:

    To make this simple I am taking a small table which has 4 columns for better understanding.  From there we need to fetch only second column data and the 4 th column data.
    Excel VLOOKUP Tips and Tricks
    So i am using the VLOOKUP formula as mentioned below.
    =VLOOKUP($A9,$A$1:$D$6,MATCH(B$8,$B$1:$D$1,1)+1,FALSE) in column B9 and copy the same in C9, B10 and C10 also. In cell B9 it returns 31% which is the % of Commision of Banana from the table and in C9 it returns 120 which is quantity. Similarly, it returns 15% and 50 in B10 and C10 respectively.
    Explanation on the Above VLOOKUP formula with MATCH function:
    The lookup value column is made constant and the data range both rows and columns made constant. So where ever we paste the data, the lookup range should be the same and lookup value will be on the same column but it may be in different rows as we are not made row index constant.
    The third argument of vlookup,  ‘index_number’ which refers to the column from where vlookup should fetch the data, we have used MATCH  function. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. In our example, match function returns relative position of the required column header in the headers of the our main database headers. The MATCH function position number starts from 0 and VLOOKUP starts with 1. So we are adding 1 to the relative position returned by MATCH function.
    As I said earlier, this is the one of  simple yet very useful combination of  formulas in a real time excel profession. I have faced many situation in my professional life, where it takes a huge time if we use classic vlookup function to fetch few data from a huge database. This saves our precious time also accuracy as there is very less chances for the error. Do let me know, if this Excel tip helpful for you and also if you face any problem using this tip.

  • Nokia Lumia 620 Advantages and Disadvantages

    Nokia Lumia 620 is the most affordable Windows 8 phone as of now. Though it got delayed to enter the Indian market, it is making news for its performance for the price it is offered.  The latest Lumia 620 price in India is ₹14400.  Before looking into the advantages and Disadvantages of Lumia 620, let us have a quick look into the features of Lumia 620.
    Budget Nokia Lumia Phones
    To Start with Nokia Lumia 620 comes with 3.8 inch  ClearBlack display screen with a screen resolution of 800*480 pixels which makes 246 ppi pixel density. Interms of numbers it is not spectacular. But it offers very good sunlight legibility and wide viewing angles. Colors are vivid and images looks sharp.Considering the price of Lumia 620, there is no phone has such a decent display from any of the well established brands.
    Nokia Lumua 620 comes with a 1GHz dual core Krait processor with 512MB of RAM and Adreno 305GPU.  For storage it offers 8GB on board storage, 7GB SkyDrive Space and support for micro SD card support upto 64GB. For connectivity, device has 2G, 3G, Dual band Wi-Fi, Bluetooth 3.0, NFC and microUSB 2.0. There is 1300 mAh battery which powers the phone is capable to hold the battery life a full day with normal use.
    Nokia Lumia 620 Smartphone
    Nokia Lumia 620 Camera is another plus point. It has got 5MP primary camera with LED flash which can record 720p HD videos at 30fps. You cannot compare the quality of the Lumia 620 camera with high end phones. The low light performance is just average. Having said that, it is capable of recording 720p HD videos unlike other similar priced phones which provides only 420p videos such as Samsung Galaxy S Duos which is slightly priced higher than this model. The Nokia Lumia 620 also features a front facing camera which can be used for Video calling, Video Chat and Self portraits. Now let us look into the some of the key disadvantages of Lumia 620.
    Disadvantages of Nokia Lumia 620:
    Lack of ample Apps: The Windows Phone 8 does not have as many apps as compared to iOS and Android. Also, it is observed that, some of the apps and games such as Angry bird is available only in paid version. The number of apps count is increased but there is no match compared to Android and iOS. It is not a big deal if you are a casual gamer.
    Below average low light camera performance: As mentioned above, the snaps taken in the indoors with low light conditions, looks very soft. There is a lot grains.
    Complicated SIM card slot: The sim card slot in Nokia Lumia 620 is not placed conveniently. It takes a bit of understanding before inserting the SIM. It could have better, if Nokia made it simple.
    Advantages of Nokia Lumia 620:
    Powerful processor: It has got the 1GHz dual core Krait processor which makes phone to run apps and games smoothly. Even the HD video play back is good.
    Good Display Screen:  The Lumia 620 has got very decent display screen. With its ClearBlack technology, the outdoor readability is very good.
    Pre installed Nokia Apps: Nokia Lumia 620 stands apart from other Windows phones because of its Nokia propriety apps. For example, the Nokia maps or HERE maps provides outstanding offline navigation system. The City lense app shows the nearest worth visiting places. There are many apps like this but mentioned few.
    Ability to Play vast media formats: Nokia Lumia 620 plays full HD videos and all formats including MOV and AVI were natively supported by the phone. Even playing the audio and video from the SD card also very smooth.
    Summary: There is no phone is perfect and each phone has its downsides. Nokia Lumia 620 also no exception to it.  It all depends on the budget of ours and the features we are looking for. Some of the features looks interesting but not useful on day to day life and we use very rarely like the Smart Stay feature in Galaxy S4. In my opinion, Nokia Lumia 620 is a great phone for the price it is offered if you are looking for a phone which is good for making and receiving calls, playing casual games and using it as navigation tool.  You can read more detailed  info on this in Nokia Lumia 620 Review.