Tech2 Touch

Tag: Excel

  • How to Round a Number to Closest ten in Excel

    Rounding a number in Excel is very easy. There are situation wherein we need to round a number to closest 10 or 100. For example, if we have a number 115 then 120, if we have 112 then 110. For this there is a dedicated function in Excel called MROUND which comes useful for such scenario. Let us learn more about the function MROUND in Excel.

    Syntax of Excel MROUND function

    MROUND(number, multiple)

    Where,

    • Number Required. The value to round.
    • Multiple Required. The multiple to which you want to round number.

    Note: MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.

    Example:

    Below are the few useful examples of MROUND in Excel.

    1. Rounding a number to multiples of 10

    =MROUND(112, 10) Rounds 112 to the nearest multiple of 10 which will return 110

    =MROUND(116, 10) Rounds 116 to the nearest multiple of 10 which will return 120

    2. Rounding a number to multiples of 5

    =MROUND(112, 5) Rounds 112 to the nearest multiple of 5 which will return 110.

    =MROUND(116, 5) Rounds 116 to the nearest multiple of 5 which will return 115.

    =MROUND(118, 5) Rounds 118 to the nearest multiple of 5 which will return 120.

    There are other two Excel function which are very similar to this function but with slight change on the functionality.

    1. CEILING(number,significance)

    This will Returns number rounded up, away from zero, to the nearest multiple of significance. For example

    CEILING(112,5) will return 115

    CEILING(112,10) will return 120

    CEILING(115,5) will return 120.

    2.FLOOR(number,significance)

    This will Rounds number down, toward zero, to the nearest multiple of significance.  For example,

    FLOOR(112,5) will return 110

    CEILING(115,10) will return 110.

    So in summary, MROUND Excel function is useful when we need to round number up or down to the nearest multiple. Whereas if you want roundup decimals then ROUNDUP function is useful. For rounding down decimal ROUNDDOWN excel function is useful. Depending on the requirements, these Excel functions will help us to save our time.

  • Find matching value in a row and return column name in Excel

    When we work in excel sheets, we are required to find a matching value in a row and return the Column name. For example, we need to calculate the lowest priced store from the price table of multiple stores for multiple products. Another example can be found at the primary reason for exits from the exit feedback form analysis. In these cases, even multi-condition vlookup doesn’t help. We will take the first example and find out how to solve this using the Excel formulas.

    Recommended Reading:

    Conditional VLOOKUP with MATCH In EXCEL
    Example:

    Excel Lookup

    In the above table we have to return the company name which has lowest price for that product. For this we need to find the column which has the lowest price. So we need to use min(Range) excel worksheet formula. This will give us which is the lowest value. Now we need to find the relative position of the value. For this, match worksheet formula is useful. Now have to get the column name of that position. For this we need to use index function. The final formula will become like below.

    =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) which returns Company A.

    How it works?

    MIN(B2:E2) returns 16999.
    MATCH(MIN(B2:E2),B2:E2,0) returns 1
    INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0)) returns Company A as column 1 in range B1 to E1 is B1.

    Note: The column range for index and match should be the same. Only the row numbers can be different. As match function returns relative position data, the column range should be same for Index function also.

    You can tweak this function according to your requirements. You might required to find the column name when you know the exact value in the row. In that case, you need to replace min function with required value.