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 »

Sum – Year to Date

The problem here is summing sales figure from the start of the year to a particular month Sales of 2 products (Item1 and Item2) is given for an entire year – Jan to Dec. The objective is to find cummulative sales till a particular month. For instance the input is Item1 and Jun – where [...]

Read Full Post »

Evaluating step functions

This problem is on step functions. Certain utility company charges me in this manner For the first 25 units of consumption, the rate is 212 per unit, for next 35 units, the rate is 270 per unit and so on. I have calculated the total amount payable for certain unit of consumption by using sumproduct [...]

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 »

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 »

Multiple extraction through vlookup

The problem here is extraction of multiple results from a two column array through a vlookup   The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below Start by sorting on region (column B) [...]

Read Full Post »