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 »

Unraveling a table

Here is the problem that I faced         I had to extract the entries in the table row-wise and populate this in a single column. Here is one way of doing this: Enter A8=OFFSET($A$2,INT((ROW()-ROW($B$8))/5),MOD((ROW()-ROW$B$8)),5),1,1) Drag down till the entire table is unravelled. What if we had to unravel the table column wise [...]

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 »

Reversing order of a table

I had saved monthly data by adding months on top of the heap (as in table) Now while presenting this data, I wanted the data to appear in chronological order, January in row 1, February in row 2 and so on.     I achieved this by using the offset function. In cell F3 enter [...]

Read Full Post »