Assume a single row of data with numbers and blanks appearing at random intervals. The objective is to sum the largest 5 of last 10 numbers in that row. Solving this problem entails multiple steps: Identify the last 10 numbers in that row i.e. starting from the right hand side, identify the last 10 numbers […]
Read More →
{ 0 Comments }
Consider this simple two column table showing text entries in column A and the corresponding numbers in column. Assume this data is in range A2:B11 (headings are in A1:B1). text Value A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 0 The objective is to […]
Read More →
{ 4 Comments }
Assume a simple two column dataset with dates in column A and numbers in column B. The dates in column A are from January 1, 2013 to December 31, 2016 and numbers in column B are for the period January 1, 2013 to December 31, 2015 (there are no numbers for January 1, 2016 to […]
Read More →
{ 8 Comments }
The objective is to assist a Store Manager with computing potential sales across different products and colours. To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]
Read More →
{ 11 Comments }
Assume a simple two column data range as follows: Product Amount A 1 S 2 D 3 E 4 Product A F 5 G 6 R 7 Product B q 8 w 9 s 10 d 11 c 12 v 13 b 14 Product C In the table above, one may want to compute the Product […]
Read More →
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 →
{ 2 Comments }
Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term). Since multiple investments can be made in one fund, there can be multiple line items […]
Read More →
{ 7 Comments }
In range E5:E13, assume the entries are 1 2 #DIV/0! 4 5 6 #N/A 8 9 In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function […]
Read More →
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 →
{ 16 Comments }
Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads. While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two. If […]
Read More →
Sum the largest 5 of the last 10 numbers in a row ignoring blanks
{ 2 Comments }