Compute Day Sales Outstanding (DSO)


A financial concept, Day Sales Outstanding (DSO), is a measure of the average number of days that a company takes to collect revenue  after a sale has been made.   For further clarification, read here. Herein, i have presented the solution for two different data layouts.  Further, Layout 2 has two further divisions – one for computing […]

Read More →

Conditional testing without lengthy nested IF functions


Many a times writing nested IF() statements become cumbersome, lengthy and highly prone to errors.  In worksheet named “Question”, one would like to avoid using spare columns for computation of landed value for the three product sources (“CKD”, “LMP”, “IMP”).  While one can use a nested IF() statement, it would be unmanageable because of the length of the […]

Read More →

Sum highest n numbers based on conditions


Assume a two column database with names in column A and numbers in column B.  Names in column A may be repeated.  If a user types a certain name in a cell, a formula should sum the highest three values from column B for that name. Depending upon the version of MS Excel which you are […]

Read More →

Return closest numeric match


In range A2:A6, the numbers are 7.5, 2, -12, 11.2 and 8.  In cell B8, a user enters 8.  One may want to answer the following questions: 1. Return the closest number which is less than or equal to the number in cell B8 2. Return the closest number which is greater than or equal to the […]

Read More →