Search for Excel mini or maxi cells

Continuing the discussion here:
https://forums.commentcamarche.net/forum/affich-27949689-minimum-et-maximum-locaux#top

--
Retirement is great! Especially in the Caribbean ... :-)
☻ Raymond ♂

Whoa! This looks strikingly like standard deviation calculations, but I've forgotten the laws that allow for all that. I hope a statistician from the forum can help you.
In a more general sense, the page that covers all these functions at Crosoft is at this address:

https://support.microsoft.com/fr-fr/office/fonctions-statistiques-r%c3%a9f%c3%a9rence-624dac86-a375-4435-bc25-76d659719ffd?ocmsassetid=hp010342920&correlationid=33aa31ce-bb46-42ae-a099-68f8b3ba7e74&ui=fr-fr&rs=fr-fr&ad=fr

Best regards,
Jean

I'll take a look at it, thanks.

Hello

Not sure I fully understand what you mean by "local minimum"
Is it a point bounded by two points with greater ordinates?
Is it a point where the ordinate is below a given threshold?
Could you send a significant part of your file in Excel 2003 format and include the link obtained in your next message?

Best regards

Hello again, Franku 93,

Back on the site: 6000 points, is it indeed in the form of a scatter plot?
Local minima and maxima, we know what they are: the smallest and largest y-value for a given x-value

The average of all this mess, the regression line, is classical calculation, but if I understand correctly, you want the average of the local maxima and the average of the local minima...
Now, you are entering into trend curves at the limits of the scatter plot, which seem to need to be treated by algorithms mentioned in the book I saw at this address:
http://www.google.fr/url?q=http://maitinebergounioux.net/PagePro/Enseignement_files/Livre.pdf&sa=U&ei=idywUZiUA8WGOMCtgcgP&ved=0CDIQFjAH&usg=AFQjCNHm6zZdHQwvwAzpeHyjFdQ8HAlVow

One makeshift solution in Visual could be the following:

- Database of points in columns with: x-values, y-values, extraction of the y-values meeting the desired criterion for maxima, extraction for minima
- Define a basic unit for the x-values of the scatter: it's up to you to decide

Then, for the calculation of local maxima:
- For each unit of x, check if there is a corresponding y-value (does the point exist?)
- If not, move to the next x-value,
- If there is only one data point, store the y-value
- If there are multiple data points (multiple points on the same x-value), store the y-value of the highest rank,
- Move to the next x-value
etc....
End with the average of the extraction column

Same for local minima

See the VBA programmers on the site, knowing that the skeleton of the thing is to embed an analysis of the type:

Check that the columns are sorted in ascending order using as sort key #1 the x-value column and as sort key #2 the y-value column - Watch out for mixing!

For LOCAL MAXIMA:
CREATE VARIABLE n for example

FOR( n; starting at: first x-value processed; step value equal to the smallest difference of x to be determined based on the data; until.... end of the scatter)
SEARCH(in x-value column the LAST cell containing the counter value for local maxima,) (the last in case of multiple y-values present for the same x-value, hence the importance of correct sorting)
IF (this value exists) (there may be an x-value for which there is no point)
SEARCH(in the adjacent column the registered y-value)
STORE this data in the 3rd column
END IF()
(n = n + step value)
NEXT(): creates a new loop
etc... until the end of the scatter

For LOCAL MINIMA: same, store in the 4th column, modifying in
SEARCH(in the x-value column the FIRST cell that gives the lowest y-value of the scatter for this x-value)

VBA folks, have fun refining this
It's a makeshift solution, but macros can pretty much do everything except shine eggs and beat shoes into snow :-)))

Best regards
Jean