- § Which group does a name belong to? In a table where entries have a header column in a table. Method to return the column header (group) for a particular name in the table.
- § Lookup for “contains” Method to look up a value where the value is contained in the lookup array; search for a value within a cell of lookup array and return the result; sum results for all cells that contain the lookup value
- § Cell value from a grid Methods to find a particular value after looking up both rows and column; lookup both rows and columns
- § The trick of –– Trick to convert text to numbers and boolean TRUE, FALSE to 1,0 using operator ––
- § Extracting the nth occurence from an array – Method for looking up all values for a multiple repeated look-up value in an array; multiple extraction; nth occurence
- § Sum – Year to Date – Method for summing up figures till a particular month, YTD, cummulative summation
- § First, Last, Min, Max for a particular value in an array – The method gives solutions to finding the first, last, min, max results for a particular lookup value
- § DPD analysis using two condition sum – Days past due analysis, two condition sum, bucketing data, frequency function
- § Lookup value as array – Passing an array for lookup_value, why lookup over vlookup, multiple lookup_value in single lookup function, sum(lookup)
- § Converting Text to Numbers – Methodolgy to convert text to numbers. Numeric equivalent of alphabets. Summing up alphabets.
- § Converting Numbers to Words – Methodolgy to convert numbers to words. Figures to words. Especially useful in cheque printing
- § Adding errors, texts, numbers, blanks in a list – This is how you can quickly add occurences of errors, texts, numbers, blanks in a list
- § Preparing a RAG report – Methodology to prepare RAG (Red, Amber, Green) report to track completion status of various tasks or projects
- § Extract and sum vlookup results – A tip to pass an array as col_index_num in vlookup so that a single function can be used to carry out data extraction and summation
- § 10 tips for great formatting – A list of 10 formatting tips to help make your excel worksheet look great
§ Sum array with errors – tip on summing up an array which is a combination of numbers and errors
- § Step function – tip on calculating costs when cost per unit is a step function
- § Lookup to the left – one of the ways to overcome the deficiency of vlookup which does not allow lookup to the left
- § Handling errors in excel – description of various types of error, error.type, counting errors
- § Cows and Bulls – play this classic game in excel
- § Vlookup – col_index_num – tip on calculating col_index_num without having to count it on excel
- § Insert spaces – revisited – refining the solution earlier given for this post on inserting spaces within an array
- § Troubleshooting vlookup – description and solution of various types of errors encountered while using vlookup
- § Unraveling a table – unraveling an n x m table to put the data in a single row or column
- § Generating a series of numbers – one of excel tips which finds its use in a number of solutions given later
- § Multiple extraction through vlookup – solution on extracting multiple data where the lookup_value is repeated with different results
- § Remove blank cells – tip on removing blank cells within an array
- § Remove spaces from a list – tip on removing spaces within contents in a cell
- § Financial Functions – usage of PMT, IPMT and PPMT to calculate EMIs (equated monthly instalments) of a loan
- § Reversing order of a table – toppling the table upside down
- § Separating names into last and first names – tip to separate first names and last names when they appear together in a single cell
- § Create spaces within a list – tip to add blank cells within a list
- § Frequency distribution – help on the use of statistical function – frequency distribution
- § Identifying duplicates in a list – tips to identify duplicates within an array
- § Change date format – to change date from dd/mm format to mm/dd format and vice versa
- § Populate blank rows – tip to populate blank cells within an array
Leave a comment