Let’s learn how to find running minimum values, aka cumulative minima, using an array or a non-array formula in Google Sheets.
We can use MIN non-array and DMIN or the new SCAN array formulas to calculate cumulative minima in Google Sheets.
If you invest in stocks, you may find the said formulas worthy.
Because, using them, you can find how far off a particular or current point is from the historical minimum throughout the day.
E.g.:-
I have the share price of an automotive manufacturing company throughout the day in an array (B2:B11) in Google Sheets.
The running minimum values are in C2:C11, and how far off the cumulative minima from the share price at each point are in D2:D11.
Note:- For finding cumulative maxima, we can use MAX and DMAX or SCAN formulas which I have already detailed here – Running Max Values in Google Sheets (Array Formula Included).
What are the formulas in use in cells C2 and D2?
Here we go!
Cumulative Minimum Using MIN Non-Array Formula
In cell C2, you may insert the following MIN formula and drag it (fill handle) down.
=min($B$2:B2)Here is the code for cell D2.
=B2-C2It is yet another drag-down formula.
DMIN Array Formula to Get Running Minimum Value in Google Sheets – Old
We have a 1-by-10 vector of share prices throughout the day in the range B2:B11.
As you may know, we can get the minimum stock price using the following DMIN, which will return 99.
=dmin(B1:B11,1,{if(,,);if(,,)})It’s equal to using =min(B2:B11).
Note:- To learn the {if(,,);if(,,)} use, please read Two Ways to Specify Blank Cells in Google Sheets Formulas.
Then how do we use DMIN to find the running minimum value in Google Sheets?
Let’s code the DMIN formula step-by-step so that you can learn it quickly.
The Logic Behind Finding Cumulative Minima in Google Sheets
We have data in a single column range (10×1-Matrix), and that is in B2:B11 (10 rows and 1 column).
We will transform it to a 10×10-Matrix (10 rows x 10 columns) to find the min of each column and output it vertically.
This way, we can find the running minimum using an array formula in Google Sheets.
So there will be two steps.
- Transform data 10×1-matrix to 10×10-matrix.
- DMIN each column.
1. Transforming Data
We can use my 1-12-123-1234 Patterns formula to transform the data.
The Formula in E2:
=ArrayFormula(TRANSPOSE(if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11),)))
We may further require modifying this 10×10-matrix.
We require adding one more row at the top of it, which will act as field labels in DMIN.
It’s necessary as DMIN is a database function. Any values will be OK to use as field labels.
So we will transpose the share prices in B2:B11 and use them as field labels.
The formula in E1 (remove E2 formula):
=ArrayFormula(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11),)}))
Now we can use a DMIN array formula in cell C2 to find the running minimum values.
2. Cumulative Minimum Using DMIN Array Formula
The C2 Array Formula:
=ArrayFormula(DMIN(E1:N11,sequence(rows(B2:B11),1),{if(,,);if(,,)}))The sequence part returns the numbers 1 to 10, which are the field IDs (there are ten columns in our database).
Finally, replace E1:N11 in the above formula with the corresponding 1-12-123-1234 pattern formula in cell E1.
That’s our final cumulative minima formula in Google Sheets.
=ArrayFormula(DMIN(transpose({B2:B11,if(sequence(10,10)/sequence(10,10)+sequence(1,10,row(A2)-1)<=row(A2:A11),transpose(B2:B11))}),sequence(rows(B2:B11),1),{if(,,);if(,,)}))In the above formula, the ranges are closed (finite). To make them open (infinite), please do the modifications as follows.
| Closed Ranges | Open Ranges | Occurrences | |
| 1 | B2:B11 | indirect("B2:B"&MATCH(2,1/(B:B<>""),1)) | 3 |
| 2 | A2:A11 | indirect("A2:A"&MATCH(2,1/(B:B<>""),1)) | 1 |
| 3 | sequence(10,10) | sequence(rows(indirect("B2:B"&MATCH(2,1/(B:B<>""),1))), | 2 |
| 4 | sequence(1,10,row(A2)-1) | sequence(1,rows(indirect("B2:B"&MATCH(2,1/(B:B<>""),1))),row(A2)-1) | 1 |
I’ve modified the formula accordingly and placed it in my sample sheet down below.
SCAN Array Formula to Get Running Minimum Value in Google Sheets – New!
The new SCAN function is the best array solution to get running minimum values in Google Sheets.
We usually use it for running sum as it takes an accumulator (initial_value) and moves row by row in the range (array_or_range).
Syntax: SCAN(initial_value, array_or_range, lambda)
Before using SCAN for the said purpose, we must first know the cumulative minimum (Cummin).
Cummin is the comparison of the current value with the previous value. It returns the smaller one.
Cummin Array Formula (C2):
=scan(B2,B2:B,lambda(a,v,if(a<=v,a,v)))Logic:
The initial_value in the accumulator (a) is the first value in the series (array or range).
In the first row, the IF logical formula tests whether initial_value (a) <= first value in the array_or_range (v) and returns the same value as both are the same.
In the second row, the formula tests a <= second value in the array_or_range (v) and returns the minimum value. It is the new value in the accumulator.
The above logical tests, i.e., a<=nth row value, continue in every row in the series (array or range).
We can also use an array formula to find how far off the cumulative minima are from the share prices at each point.
We can do it effortlessly by emptying D2:D and inserting the =ArrayFormula(if(len(B2:B),B2:B-C2:C,)) in cell D2.
Related: Row-Wise MIN Using DMIN in Google Sheets.
That’s all. Thanks for the stay. Enjoy!
