CODEWITHSUNDEEP
excel-formulamax
DeltaIV
DeltaIV

Reputation: 5646

EXCEL find the last relative maximum in a array (formula, not VBA)

I have a range containing values such as:

169.7978
168.633
168.5479
168.7819
167.7407
165.4146
165.1232

I don't need the maximum value of the range, i.e., the first cell in this example), but the last relative maximum, which in this case is the fourth cell. Is there a way to get this value without having to write a VBA macro? The formula must be general enough to work with a multiple number of maxima.

Upvotes: 0

Views: 1377

Answers (1)

Peter Vandivier
Peter Vandivier

Reputation: 681

It may be a bit limited, but you may start somewhere as below.

Stated array in the OP is:

+----------+---+
|    y     | x |
+----------+---+
| 169.7978 | 1 |
| 168.633  | 2 |
| 168.5479 | 3 |
| 168.7819 | 4 |
| 167.7407 | 5 |
| 165.4146 | 6 |
| 165.1232 | 7 |
+----------+---+

RelativeMixMaxDistro

Given this, you can find direct adjacency relative min/max with the following helper columns

RelativeMaxMinValues RelativeMaxMinR1C1

Assign a Global_Rank helper column and look for y distro identical trend on both adjacent f(x) with the following formulas ( assuming your data is sorted by the x index )( formulas from Row 2 and filled down ).

RelativeMax: =IF(AND(D2<=D1,D2<=D3),"RelativeMax","")

RelativeMin: =IF(AND(D2>=D1,D2>=D3),"RelativeMin","")

Modify as needed. Hope this helps.

Edit:

Although...

If you're going to assume the data is ordered properly, you could also just use =IF(AND(B2>=B1,B2>=B3),"RelativeMin",IF(AND(B2<=B1,B2<=B3),"RelativeMax","")) and skip all the malarkey. This should work with multiple maxima/minima. Please report back with results from your dataset!

Upvotes: 1

Related Questions