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-based non-array and DMIN-based 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 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 Minima 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-C2`

It’s is yet another drag-down formula.

**Find the Running Minimum Value Using DMIN Array Formula in Google Sheets**

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 to add 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. DMIN to Find Running Min Value in Google Sheets (Array Formula)**

The C2 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 below.

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!

## No comments:

Write Comments