Tags: SUMPRODUCT

Compute transaction fee based on a tiered pricing model

{ 0 Comments }

Consider a simple dataset as shown below: For each tier, the tier rate is incrementally applied to the volume within the tier volume range.  Given the following transaction volumes, one may want to compute the transaction fee The expected result is shown below As one can observe, for a transaction value of 400,000, the fee […]

Read More →

Determine cumulative interest payable on an annuity with varying time periods

{ 0 Comments }

Imagine a fixed monthly amount due to an Organisation for services rendered to various customers.  While an invoice is raised every month by this Organisation, not all pay up the dues on time.  For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum.  The objective is to determine cumulative […]

Read More →

Determine number of learners who have completed different stages of multiple online courses

{ 4 Comments }

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation: Learner Stage completed Course Bill Stage 1 Public Speaking Bill Stage 2 Public Speaking Bill Stage 3 Public Speaking Susan Stage 1 Effective Communication Bob Stage 1 Public Speaking Bob Stage 2 Public Speaking Sheila […]

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 →

Summarise data with multiple wildcard OR conditions

{ 0 Comments }

Imagine a dataset as shown below Status Text Number Active bat 1 Passive erq 2 Passive cat 3 Active enm 4 Active dog 5 Suppose the data is in range A3:C7 (headings are in A2:C2).  The objective is to add numbers where the following conditions are met: 1. Status should be Active; and 2. Text […]

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 →

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{ 8 Comments }

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time. The task is to create a simple […]

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 →