Problem: We want to rearrange the contents of the list in column A in reverse order. Solution: Use the OFFSET, COUNTA, and ROW functions as shown in the following formula: =OFFSET($A$2,COUNTA($A$2:$A$7)-ROW()+1,0)) Example: List_____Opposite List___Formula Result 1________5_______________5 2________4_______________4 3________3_______________3 4________2_______________2 5________1_______________1 … Continue reading →

MUNIT function Munit function has been introduced with Excel 2013. It returns the unit matrix for the specifid dimension. For example, you can select a1:c3 range and while selection is there, type = Munit(3), press Ctrl + Shift + Enter and … Continue reading →

BITOR function This function has been introduced with Excel 2013 and it returns bit-wise “OR” of 2 numbers. For example, binary representation of 3 is 11 and for 10 it is 1010 and we can check that we have 1 … Continue reading →

UNICODE is a new function introduced in Excel 2013. This function is used to return the code number of the first character of the text in reference. Syntax:- UNICODE(text) where text is the character string for which you need the … Continue reading →

In this article, we will learn where and how we can use Excel formula IFERROR. What is IFERROR function? The IFERROR function Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the … Continue reading →

Problem: Columns A & B show annual profits for a number of years. We want to calculate the average rate at which profits grew each year. Solution: Use the AVERAGE function in the following Array formula: {=AVERAGE((B3:B5-B2:B4)/B2:B4)} For more … Continue reading →

BITAND function This function lunched with Excel 2013 and it returns a bitwise “AND” of two numbers. Example:- The binary number for 5 is 101 and for 9, it is 1001 and bits of these two number matches at rightmost … Continue reading →

Problem: Range A4:A8 contains a list of numbers that is frequently changed by the addition or removal of numbers. Consequently, the current rank of each number is constantly changing. We want to create a formula that will automatically update the … Continue reading →

roblem: One column contains mixed values of positive and negative numbers, needs to be Separated into two columns, in the first column positive values in second column negative values. Finally, add a formula to return running balance along the cells … Continue reading →

Problem: Column A contains a list of numbers. We want to find the minimum value in the range between row 2 (first value in the list) and each row number specified in column B. Solution: Use the MIN and INDIRECT … Continue reading →