How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Thursday

Find the Running Minimum Value in Google Sheets

Posted by   on

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?


Finding Running Minimum Value aka Cumulative Minima in Google Sheets

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.



  1. Transform data 10×1-matrix to 10×10-matrix.

  2. 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),)))

Transforming Data for Running Minimum

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),)))

Database for DMIN for the Test

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 RangesOpen RangesOccurrences
1B2:B11indirect("B2:B"&MATCH(2,1/(B:B<>""),1))3
2A2:A11indirect("A2:A"&MATCH(2,1/(B:B<>""),1))1
3sequence(10,10)sequence(rows(indirect("B2:B"&MATCH(2,1/(B:B<>""),1))),2
4sequence(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.


Sample Sheet 251121


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

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!