Tags: AND

Distribute projected revenue annually

{ 5 Comments }

Here is a dataset showing Project wise forecast of open opportunities. Topic is the Project Name Est. Close Date is the date by when the opportunity would be closed i.e. the project would be won from that Client Duration is the time (in months) for which the project would run Amount is the total amount […]

Read More →

Alter the behaviour of a filter/slicer from OR to AND

{ 3 Comments }

Here is a simple two column dataset which shows EmpID in column A and DateWorked in column B.  My objective is to filter the table to show those employees who worked ONLY on August 17 and August 18.  I’d like to exclude employees who: Did not work on both these days; and Worked on both […]

Read More →

Identify buy and sell break points

{ 0 Comments }

Assume a two column dataset with Date in the first column and Price in the second one.  The purpose is to identify times to buy and sell – buying would be just after the lowest low is confirmed and sell before or just after the highest high is in place. Confirmation is achieved through crossover of […]

Read More →

Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer

{ 11 Comments }

Scenario 1 In a Stock Broking business (Firm), Customers deposit margin money through cheques. Although cheques are received from customers at branches, clearing (realization) of the same normally takes 3-4 days.  On one hand, the Firm wishes to allow trading to the customers against the deposited cheques from the day it receives the cheque, on other hand there is […]

Read More →

Compute configuration count using Set Theory and Venn Diagrams

{ 0 Comments }

Assume a table which lists attendees for a Company’s Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]

Read More →

Determine cumulative expenses per employee when per diem rates vary by block of dates

{ 8 Comments }

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the […]

Read More →

Derive end date and time from start date and time, office working hours and lunch breaks

{ 25 Comments }

Given the following inputs/restrictions, one may want to compute the end date and time of a project: 1. Start date and time of the project; and 2. Official working hours; and 3. Lunch breaks hours Furthermore, to add to the complication, one may have different National holidays and weekend days i.e. while for some, the weekend […]

Read More →

Shade alternate band of rows in a filtered range

{ 2 Comments }

Assume the following numbers in range C5:C22.  Heading is in cell C4. Range C5:C7 – 11 Range C8:C11 – 14 Range C12:C13 – 23 Range C14-C19 – 56 Range C20-C22 – 78 One may want to colour cells for every change in number in range C5:C22 with the following two modifications: 1. Shading should be for […]

Read More →

Extract data based on customer specific dimensions

{ 0 Comments }

Depending upon customer requirements specified for width, thickness and length os material, extract a report showing all records from the master stock-list which meets the requirements. The question and solution have been elaborately explained in this workbook.

Read More →

Determine stock transfer from multiple locations

{ 0 Comments }

The objective is to determine the quantity of stock (of a particular type) to be transferred from “Stock surplus” locations to “Stock deficient” ones.  If all the requirements cannot be met from one location, tap other locations.  The final output should show the location from where stock is being transferred.  Furthermore the order of determining […]

Read More →