Author: Aniruddh

  • Simple Home Remedies for Cold And Cough

    During the winter season, the cold and cough is a common everywhere. At times it gives lot of trouble such as cough during nights. The cold and cough are nothing but the reaction of our body to the external particles. Some do have higher resistance and some have low resistance.  Here is the quick list of common causes for Cold and Cough.

    Causes for Cold And Cough

    There are many reasons for a cold and cough. Here is some of the most common causes.
    1. Change of weather condition which intern results or favors the growth of viruses
    2.Exposure to dust which is gives allergic cough and cold
    3. Infection through un purified water
    4. Restlessness. Working till late night and getting up early which will intern reduces the sleeping ours.
    5. Infection through the neighbours or friends colleagues
    These are some of the points just to point out, There are many other reasons too. Let us look into the simple remedies to get relief from the cold and cough. It is always better to consult a doctor to get proper prescription. Below are few remedies which are commonly used to get relief.

    Remedies for Cold and Cough

    1. Drink lot of water. This will help in preventing the dehydration and keep your throat moisturized. It will ideal to drink hot water rather than cold water. It is always good to drink at least 8-10 ounces glass of water daily
    2.  Steam Inhaling:  Inhaling the steam will help in easing your congestion and drippy nose. To do this, just breath in steam of a boiling water from your nose. Add bit of salt to the boiling water to get better relief during sinusitis.
    3. Honey, Lemon and Sugar: This is the one which my grand father was telling during heavy cough. Just extract one full lemon. Dont add water. Add same quantity of  Honey, Add 5-8 spoon of sugar and mix it. It takes around 10-15 minutes to mix well.  Then take this mixture slowly. This stops cough almost immediately.
    4. Nasal Irrigation:  It also called as nasal lavage or nasal douche or Neti  is the personal hygiene practice in which the nasal cavity is washed to flush out excess mucus and debris from the nose and sinuses. The practice is generally well-tolerated and reported to be beneficial with only minor side effects. Nasal irrigation in a wider sense can also refer to the use of saline nasal spray or nebulizers to moisten the mucous membranes.According to its advocates, nasal irrigation promotes good sinus and nasal health. Patients with chronic sinusitis including symptoms of facial pain, headache, halitosis, cough, anterior rhinorrhea (watery discharge) and nasal congestion are reported to often find nasal irrigation to provide relief. In published studies, “daily hyper tonic saline nasal irrigation improves sinus-related quality of life, decreases symptoms, and decreases medication use in patients with frequent sinusitis”, and irrigation is recommended as an adjunctive treatment for chronic sinonasal symptoms
    5. Practice Pranayama: Regular practicing of pranayama helps in strengthening the lungs. This will help you to protect from the cold.
    6. Taking Ayurvedic Health Substitutes: The Ayurvedic health substitutes such as  Chyavana Prasha, Septiline are known for increasing the immunity power of our body. Taking these will help in preventing the cold and cough, Do remember to take your doctor consultation before taking any medicines.
    7. Avoid Smoking: Smoking is not good for health. During cough and cold, try to avoid smoking of cigarettes it increases the cough.
    8. Take Ginger tea or turmeric milk: This will give relief during heavy cold.

    Disclaimer: This article is just for the knowledge purpose only. As mentioned in the post, it is  always recommended to meet a doctor as soon as you are suffering from any illness. Self medication is always dangerous. Yoga or pranayama should be performed under guidance of a expert trainer.

  • VLOOKUP in Excel Tips and Tricks

    We already learned on how to use VLOOKUP in Excel.  In my earlier different posts, i have shown, the different ways you can use vlookup functions. This post is the summarisation all the things which we have already discussed.

    VLOOKUP in Excel Tips and Tricks

    Here is the list of few VLOOKUP in Excel tips to use the excel worksheet function more effectively.

    1. Use named Ranges:  the vlookup function with normal range variables looks very complicated. To avoid this, use named ranges.  With named ranges, you can do dynamic ranges which saves lot of memory and by that, the time. You can read more dynamic ranges in excel here.
    2. 2 way or conditional vlookup: The default vlookup accepts only single criteria lookup. But using the match function, you use vlookup function which looks for the given value in row as well as in columns. The syntax is same as the normal vlookup apart from inserting match function instead of index number. The match function returns the reference number of the column. To learn more on this read my post on conditional vlookup.
    3. Handling error values in Excel: When the lookup value is not found, vlookup returns, #na. You can remove this by using iferror function. This will make your excel template more clean and easy to perform calculations
    =IFERROR(VLOOKUP(B16,A2:C6,2,FALSE),0)
    4. Use Wildcard characters for related lookup: You can use related lookup using the last argument as TRUE. but, it has a downside. The range should be in ascending order. The wildcard character will help in these situations. Below are the few examples.
    =VLOOKUP(“Galaxy*”,A2:C8,3,FALSE)- returns the value from the range which starts with Galaxy. If there are multiple Galaxy are there, then it will take the first one.
    =VLOOKUP(“????”,A2:C8,3,FALSE) this look for a value with the lookup value length is 4.
    Similarly you can use most of the wildcard characters in vlookup also.

    If you find these tips on VLOOKUP in Excel, or have any queries, feel free to leave feedback in the comment section below.

  • Creating conditional drop down list in Excel

    In my earlier post, I have shown you how to create a simple drop down list in Excel. That meets most of our day to day requirements. However, when we create a dashboard or a template, we need to create a dependent drop down list or a conditional drop down list in Excel. That second drop down content should be changed based first dropdown content. It is very is to implement. So let us explore the way to create conditional drop down list in EXCEL.

    Steps to Create conditional drop down list in Excel

    To create a conditional or a dependent drop down list you need to use basic HLOOKUP function if your lists are organised vertically or vlookup function if your list is organised horizontally.

    Let me you show you how to create a dependent or conditional drop down list with a simple example.

    We have three different mobile manufacturers name in the top and list of model names down the list in sheet1 as shown in the sample table below.

    First you define the lists with the name of the manufacturer. In this example, I have created the name ‘Samsung’ for the range A2: A10, ‘Nokia’ to the range B2:B10, ‘Sony’ to  C2:C10. Like this you can create any number of lists.  You create these named ranges by going to ‘Formulas->Define Name’.

    Now we need to create the first drop down list in sheet2 where we want to create the dependent conditional drop down.

    • The first drop down list is the simple one. Just go to Data->Data Validation->Data Validation.Under validation criteria, select list under ‘Allow’ and select the source as the headings of your lists. That is Sheet1, range A1:C1. Your first drop down list is ready.
    • Now the second drop down list is dependent list. To achieve this we use, INDIRECT function and HLOOKUP function.
    • Go to  Data->Data Validation->Data Validation. Select list and the source enter the formula as mentioned below and click Ok.
    • =INDIRECT(HLOOKUP($A$2,Sheet1!$A$1:$C$1,1,FALSE))
    • Your dependent drop down list shows now. As you change the first drop down, second drop down list changes.

    How this works

    The INDIRECT functions, tells the excel that, the source is a reference and not a value or text. HLOOKUP function returns the name of the group based on the first drop down. Similarly you can do it with VLOOKUP if your lists are organised in vertical lists.

  • Excel Tip To Calculate The Number of Work Days Between Two Dates

    There are many situation where we need to calculate the difference between two dates. We can get the no of days by subtracting the one date by one date. However, that gives the total days between these dates in which includes weekly offs and Holidays. To overcome this, excel provides a dedicated function called NETWORKDAYS.
    The Excel NETWORKDAYS function calculates the difference between two dates excluding weekend days(Saturday and Sunday).  You can even provide the list of holidays in between the two dates two excludes that many days. The holidays are different for different regions. So excel by default will not be able find which dates are Holidays. Because of this we need to give list of Holidays to get the exact working days excluding holidays and weekly offs.
    The Syntax of NETWORKDAYS Excel Function
    =NETWORKDAYS(Start_Date, End Date,[holidays])
    Where
    Start Date is the starting date and End Date is the closing Date or Finishing date.
    Holidays is the the range which contains the dates which falls under Holiday

    Example for NETWORKDAYS Function
    Let us take example in which we have 01-July-2013  in  Cell A1 and 31-July-2013 in cell B1. And the list of Holidays are kept in the range D1:D3. The formula to calculate the exact working days between these two days as mentioned below.
    =NETWORKDAYS(A1,B1,D1:D2) which gives result as 21. We have total 31 days between these days. In that we have 8 weekly offs and two Holidays. So the formula gives 31-8-2=21.

  • Format Cell using EXCEL function

    In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

    Format Cell Values using EXCEL Function

    We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

    Syntax of Excel TEXT Function

    =TEXT(value, format_text)

    Where
    Value is the number which we want to change the format.
    format_text is the required format.

    Examples of converting the format of values in Excel using formula

    We have a value 41479 in cell A1.
    =TEXT(A1,”dd-mmm-yyyy”)
    The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).

    Similarly you can use any format in which you want to display the number. Below are some more example for the same.

    • =TEXT(A1,”#,##0″) this will give output as 41,479.
    • =TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.

    Like this you can use your own formatting using this excel formula. The biggest advantage of this excel function is that you can use this formula inside another formula or function to format the output.

    For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
    =A1&B1 the output will look like this. Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
    =A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.

    You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
    =TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)

    Hope this helps to create your excel templates by using the Formula to Format Cell.