Category: CALCULATION

Sum the largest 5 of the last 10 numbers in a row ignoring blanks

{ 2 Comments }

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 →

Convert a text entry into its number equivalent

{ 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 →

Compute an average for the same day in the past 3 years

{ 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 →

Compute potential Sales of a retail outlet

{ 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 →

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 →

Prioritise investment liquidation to minimise Capital Gains

{ 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 →

Ignore errors while adding non contiguous cells of a range

{ 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 →

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 →

Perform a Variance Analysis within a Pivot Table

{ 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 →