Cell value from a grid

While we can use vlookup or hlookup to return a cell value in a particular row or column. Is there a way we can lookup from both row and column to return a particular value from a cell in a grid? Consider the case below: (Yes, I have added more colours to the table this [...]

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions The problem is given in the image sheet below   The H1Y1 performance of certain sales people have to [...]

Read Full Post »

Handling errors in excel

I have listed down the types of error in excel and their likely causes in the table below:      Error type can be found out through the formula =Error.Type(cell_ref) Count instances of error One of the ways to count the total occurence of error in an area is this {=SUM(IF(ISERROR(B2:B8),1,0))} entered as an array formula [...]

Read Full Post »

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, [...]

Read Full Post »