Days past due analysis, two condition sum, bucketing data, frequency function
Here I have carried out a days past due (DPD) analysis. This requires organizing data in various days bucket and makes use of conditional sum function.
14 cases are presented. Each case has a days past due and a corresponding amount. The objective is to put these numbers in various previously identified buckets
The days past due is defined as (0,1] which means 0<DPD<=1 and so on. This is re-written as in column F.
To calculate the amount in column G the following formulas are used, where DPD is the array B2:B15
G2 {=SUM(IF((DPD>0)*(DPD<=$F2),C$2:C$15,0))}
G3 {=SUM(IF((DPD>$F2)*(DPD<=$F3),C$2:C$15,0))} which can then be dragged till end of column
Both entered as array formula with ctrl+shift+enter
To count occurences in each DPD bucket the following formulas are used
H2 {=SUM(IF((DPD>0)*(DPD<=$F2),1,0))}
H3 {=SUM(IF((DPD>$F2)*(DPD<=$F3),1,0))} which can then be dragged to end of column
You could also use the frequency function for counting occurences
Enter formula in I2 =FREQUENCY(DPD,F2:F11)
Select I2 to I11, press F2 followed by ctrl+shift+enter
Note that the two conditions entered for summing is akin to using SUMIF with criteria as AND(cond1, cond2). However, AND criteria does not work with SUMIF.
Also one could use nested if to achieve the same result. The above format simplifies nested if through using the following logic
TRUE*TRUE=TRUE
All other permutations give a FALSE result, which is exactly a two condition AND criteria.
Have fun figuring this out 😉
Useful Excel Tips 