Tech2 Touch

Author: Aniruddh

  • Calculating Weighted Average in Excel

    Excel inbuilt Average function returns the average(or Mean) of a range of a data. However in many occasions we are required to calculate weighted average. Though Excel do not comes with a straight function to calculate weighted average. This can be done with the use of SUMPRODUCT and SUM functions. Let us start learning about weighted average with an example.

    Below is the sample data from a call center and let us calculate the Average Handle Time(AHT) for the day using different intervals calls and AHT.  The below data shows that initial hours calls were less and the AHT too was less. But as the call volume increased the AHT also increased.,

    Excel Formula Example

    The simple average formula =AVERAGE(C3:C9) returns 216.42. The problem with this is that it calculates average without considering the number calls. So we need to calculate average considering the number of calls received on each intervals. Ideally, the weighted average is the appropriate method to calculate like this calculations.

    So weighted average formula for the above example is =SUMPRODUCT(C3:C9,B3:B9)/SUM(B3:B9)
    This formula multiplies each AHT by its corresponding number calls and then adds all those products. The result is then divided by the number of days. This formula can be easily implemented to other types of calculations where weighted average is required.

  • Creating a Drop Down List in a Cell in Excel 2012

    There are situations, where we need to use restricted set of data in a list. When in data entry works are carried out by many people, chances are vast that small errors might occur. To overcome this, we can use drop down list in Excel. This not only restricts the user to enter the correct data also it helps to reduce the time to write it manually.  So for creating Drop down list, do we need to use Excel VBA feature? No;. Excel provides this feature in the form of data validation. So let us see how to create drop down list in Excel.
    Steps to Create Drop List in Excel:

    • Enter the list of items in a range
    • Select the cell that will contain the drop down list
    • Choose Data-> Data Tools->Data Validation
    • In the data validation dialog box, click the settings Tab
    • In the allow drop-down list select List.
    • In the source box specify the range that contains the items
    • Make sure that the In-Cell Drop down option is checked and Click OK
    • You are done. The drop down list is created in your desired Cell.

    Note:

    • If your list is small, then you can manually enter the list of items in the source box in the Data Validation dialog box.
    • You can copy paste the Drop down list created cell to other cells to carry the drop down list. 

  • Nokia Lumia 900 Review Price Specifications

    Nokia Lumia 900 is no more flagship model from Nokia as it has already launched new high end smartphone running on latest Windows 8 platform. Still this is one of the best hardware smartphone from Nokia with great features.  Nokia Lumia 900 runs on Windows Mobile OS 7.5(Mango) and has 1.4GHz Qualcomm Scorpion processor along with 512MB RAM and Adreno 205 GPU. Nokia Lumia 900 Price in India is ₹31,131.

    Nokia Windows Smartphones

    Nokia Lumia 900 sports 4.3 inch Super AMOLED Capacitive touch screen with WVGA screen resolution of 480*800 pixels. The device comes with 8MP primary camera with Dual LED flash capable of recording video in HD resolution of 720*1280 pixels.The camera features a 3x digital zoom as well as other useful features such as Carl Zeiss Optics, Geo-tagging and the option of auto and manual exposure. Adding to this, the device comes with 1MP front facing camera for video calling or self portrait.
    Nokia Lumia 900 is powered by 1830 mAh battery which is rated to provide 8 hours of talk time and 300 hours of standby time.  Internal storage capacitiy of this Nokia smartphone is 16GB. Unfortunately, there is no support for memory expansion through microSD card. The device comes packed with connectivity features such as GPRS,EDGE, 3G,WiFi, USB and Bluetooth.
    Nokia Lumia 900 has  GPS receiver with  A-GPS support and free lifetime voice-guided navigation.
    Summary of Nokia Lumia 900 Features:

    • Windows Phone 7.5 (Mango) OS
    • 8 MP Primary Camera
    • 1 MP Secondary Camera
    • 4.3-inch AMOLED Capacitive Touchscreen
    • 1.4 GHz Scorpion Processor
    • GPRS,EDGE, 3G,WiFi, USB and Bluetooth  connectivity
    • 16GB internal memory and 512MB RAM
    • Micro SIM Feature
    • 1830 mAh battery which is rated to provide 8 hours of talk time and 300 hours of standby time

    Advantages and Disadvantages of Nokia Lumia 900:

    Pros of Lumia 900:
    • Active noise cancellation with a dedicated mic
    • Excellent A-GPS support and free lifetime voice-guided navigation
    • Vast service network
    • Impressively deep and coherent SNS integration throughout the interface
    • Scratch resistant Gorilla glass display with anti-glare polarizer
    Cons of Lumia 900:
    • Will not get Windows Phone OS 8 update
    • No USB mass storage(Zune only file management and sync)
    • No native video calls
    • Non-user-replaceable battery
    • No memory card slot (and no 64GB version like the N9)
    • microSIM card slot
    • No native DivX/XviD support, videos have to be transcoded by Zune
    • Expensive for the hardware it offers.

    Nokia Lumia 900 sales box content: Handset, USB Charger, Data Cable, Headset, Quick Guide, User Guide, Leaflet SIM Door Key.

    Nokia Lumia 900 Specifications:

    • Operating Frequency:GSM – 850, 900, 1800, 1900; UMTS – 2100
    • OS:Windows Phone 7.5 (Mango); will get Windows Phone OS 7.8 update
    • Java:No
    • Processor:1.4 GHz Qualcomm Scorpion
    • Graphics:Adreno 205
    • RAM; 512MB
    • SAR Value:1.33 W/Kg
    • Size:68.5 x 127.8 x 11.5 mm
    • Weight:160 g
    • Display: 4.3 inch AMOLED capacitive touch screen
    • Screen resolution:WVGA, 480 x 800 Pixels,16M colors
    • Primary Camera:Yes, 8 Megapixel
    • Secondary Camera:Yes, 1 Megapixel
    • Flash:Dual LED
    • Video Recording:Yes, 1280 x 720, 30 fps
    • HD Recording:HD
    • Zoom:Digital Zoom – 3x
    • Camera Features:Carl Zeiss Optics, Auto Focus, Geo-tagging, Touch Focus, Auto and Manual Exposure, Auto and Manual White Balance, Image Editor
    • Battery Type:Li-Ion, 1830 mAh
    • Talktime:8 hrs (2G), 7 hrs (3G)
    • Standby Time:300 hrs (2G), 300 hrs (3G).
    • Internal memory:16 GB
    • Preinstalled Browser:Internet Explorer 9
    • GPRS:Yes
    • EDGE:Yes
    • 3G:Yes, 42 Mbps HSDPA; 5.76 Mbps HSUPA
    • WiFi:Yes, 802.11 b/g/n
    • USB connectivity:Yes, micro USB, v2
    • GPS Support:Yes, A-GPS with Nokia Maps, Bing Maps
    • Bluetooth:Yes, v2.1, Supported Profiles (A2DP 1.2, AVCTP 1.0, AVDTP 1.0, AVRCP 1.0, AVRCP 1.3, GAVDP 1.2, HFP 1.5, HSP 1.1, PBAP 1.0, RFCOMM 1.0, EDR)
    • Audio Jack:3.5 mm
    • DLNA:Yes
    • Music Player:Yes, Supports MP3, WAV, eAAC+
    • Video Player:Yes, Supports 3GPP, H.263, ASF, H.264, AVC, MPEG4, VC-1, WMV9, HD Video Playback
    • Sensors:3D Accelerometer, Proximity Sensor, Compass, Gyroscope, Ambient Light Sensor.

  • Using Absolute References in MS Excel

    When we apply a formula in Excel which refers to another range or cell, the cell or range reference can be wither relative or absolute.  The relative cell reference adjusts to its new location when the formula is copies and pasted in to new cell or range. An Absolute cell reference does not changes even when the formula is copy pasted elsewhere. An Absolute reference is specified with two doller($) signs. For example as mentioned below.
    Examples for Absolute references:
    =$C$10
    =SUM($A$1:$G$25)
    Examples for relative references:
    =C10
    =SUM(A1:S25)
    Normally when we use cell or range references which will be relative references. Excel by default creates relative cell references in formulas except when the formula includes cells in different worksheet or workbook.
    So where Absolute references are useful? I simple words, this is useful when we need to copy paste the formulas in different cells or ranges.

    Excel Examples

    The formula in the above example table D2 range which multiplies with quantity by the unit price is B2*C2. This formula uses the relative reference. Hence when we copy paste this formula to the next cell D3 it becomes B3*C3.
    If we use absolute reference in this formula that is instead of B2*C2, D2*$C$2, copying the formula to the below produces incorrect results. The formula in cell D3 is exactly the same as the formula in Cell D2 and returns the total for laptops and not desktops.
    Now let us use the same tablet to calculate sales tax.  The sales tax rate is stored in cell B7. Here formula in Cell E2 is D2*$B$7.

    The total is multiplied by the tax rate stored in cell B7. As this is constant for all the items, we need to give absolute references.  This reference do not change when we copy the cell.  When the same formula in E2 is pasted  to Cell E3, the formula becomes D3*$B$7. Here D3 is relative reference hence it changes but B7 remains same. 

  • Samsung Galaxy Chat B5330 Review Price Specification

    Samsung Galaxy Chat B5330 is a budget touch and type Android smartphone. The device runs on Android ICS OS.  The device come with 850Mhz processor with 512MB of RAM. Galaxy Chat sports 3 inch TFT capacitive touch screen with QVGA screen resolution. It has physical QWERTY keypad.
    Samsung Galaxy Chat come with 2MP primary camera with video recording capability. Internal memory of the device is 4GB in that only 2GB is available for apps. Memory can be expanded through microSD card upto 32GB.

    For connectivity, Galaxy Chat supports GPRS,EDGE, 3G, Bluetoth and WiFi. It is interesting fact that this phone is going to get update to Jelly Bean in the coming month according to a blog post on Samsung mobile which receives Jelly Bean Update. Samsung Galaxy Chat is available in Black, White and in pink colors.

    Overall it is a good phone considering the price tag and features. The other budget smartphones from Samsung such as Galaxy Y, Galaxy Y Pro or even Samsung Galaxy Ace Duos runs on Android Gingerbread OS and comes with 256MB or 370MB of RAM.  Except camera, this is the best buy considering the price.

    Samsung Galaxy Chat B5330 Features:

    • Android v4.0 (Ice Cream Sandwich) OS
    • 2 MP Primary Camera
    • 3-inch TFT Capacitive Touchscreen
    • 850 MHz Processor
    • Physical QWERTY Keypad
    • GPRS,EDGE, 3G, Bluetoth and WiFi
    • Expandable Storage Capacity of 32 GB

    Advantages and Disadvantages of Samsung Galaxy Chat B5330:

    Pros of Samsung Galaxy Chat:

    • Touch and type interface with decent well spaced QWERTY keypad and capacitive touch screen
    • Decent amount of storage capacity and RAM
    • Android ICS OS on a budget Samsung Phone
    • Decent battery life

    Cons of Samsung Galaxy Chat:

    • Mediocre camera
    • QVGA screen resolution display
    • No Dedicated GPU
    • No Ambient light sensors

    Samsung Galaxy Chat B5330 Sales box content: Handset, Battery, Charger, Headset, User Guide

    Samsung Galaxy Chat B5330 Specifications:

    • Operating Frequency:GSM – 850, 900, 1800, 1900; UMTS – 2100
    • OS:Android v4 (Ice Cream Sandwich)
    • User Interface:TouchWiz UX
    • Java:Yes
    • Processor:850 MHz
    • RAM:512 MB
    • Size:59.3 x 118.9 x 11.7 mm
    • Weight:112 g
    • Display: 3 inch TFT capacitive touch screen
    • Screen resolution:QVGA, 240 x 320 Pixels
    • Primary Camera:Yes, 2 Megapixel
    • Secondary Camera:No
    • Video Recording:Yes, 640 x 480, 25 fps
    • Camera Features:Image Editor.
    • Type:Li-Ion, 1200 mAh
    • Talktime:14 hrs (2G), 5 hrs (3G)
    • Standby Time:580 hrs (2G), 390 hrs (3G)
    • Internal:4 GB
    • Expandable Memory Slot:microSD, upto 32 GB
    • GPRS:Yes
    • EDGE:Yes
    • 3G:Yes, 7.2 Mbps HSDPA; 5.76 Mbps HSUPA
    • WiFi:Yes, 802.11 b/g/n
    • USB connectivity:Yes, micro USB, v2
    • Tethering:Wi-fi Hotspot
    • GPS Support:Yes, A-GPS with Google Maps
    • Bluetooth:Yes, v3
    • Audio Jack:3.5 mm
    • Music Player:Yes, Supports MP3, MIDI, eACC+, WAV
    • Video Player:Yes, Supports MP4, 3GP, MKV, H.264, H.263
    • FM:Yes
    • SAR Value:0.462 W/Kg
    • Sensors:Accelerometer, Proximity Sensor, Compass, Geo-magnetic Sensor.