ExcelHub - Learn Excel Easily

Excel Tip #025 Return the last value in a list You would have faced some situations where you want to get the last value in the list. And this list will not be static, the list will grow further and still the the last value has to be returned . Quite interesting...! There might be multiple ways but as far I know, there are 2 ways in which we can get this done. But as far I consider, there are loopholes in all the 2 methods . Let us get in detail with all the 2 methods. Method 1 This method uses the function LOOKUP . Let us see get to know about this function. LOOKUP function returns a value from a range (one row or one column) or from an array. The syntax is: LOOKUP( lookup_value, lookup_range, [result_range] ) (or) LOOKUP( lookup_value, lookup_range ) We are going to use a hidden excel function in this method which is 9e9 . So what does this 9e9 give?  9e9 is just a notation for 9,000,000,000 which is considered as the largest value in the array...

Excel Tip #016 COUNTIF Partial Matching There are many COUNT functions that are available in Excel. One of which is the COUNTIF() function. Let us see a more detailed use of it. The use of COUNTIF() function - It is used to count the number of cells in a given range which meets the specified condition. Its syntax is, =COUNTIF(range,criteria) The range field is the group of cells that you want to count. The criteria field tells the cells that needs to be counted which can be a number, expression, cell reference, or text string. Consider the below example. Example - COUNTIF() function =COUNTIF(A2:A6,"Cadbury") - This gives me 1 as the result =COUNTIF(A2:A6.A5) - In A5 we have Toblerone and the function searches for Toblerone in the list which is 1 =COUNTIF(B2:B6,23) - Here Mars chocolate has 23 and the result will be 1 =COUNTIF(B2:B6.">50") - The condition is to count cells which are above 50. We have Toblerone and Ferrero Rocher have q...