Formula for finding a range

[adamuce]

Hi,

Hope everyone is well. I hope someone can share a formula where within a range I can find the highest and smallest magnitude. An example is provided and the desired results are in the yellow shaded area



Thanks in advance

 

[AliGW]

Have a look at MINIFS and MAXIFS. e.g.

=MINIFS(B1:B8,B1:B8,"<"&0) (biggest decline)

 

[adamuce]

Have a look at MINIFS and MAXIFS. e.g.

=MINIFS(B1:B8,B1:B8,"<"&0) (biggest decline)

Thanks will have a look. Also prompted me to change the image example and include cell references

 

[AliGW]

Let us know.

 

[Peter Bartholomew]

Another emergent approach would be to use GROUPBY

= LET(
      group, IFS(changes > 0, "Increase", changes < 0, "Decline", TRUE, ""),
      GROUPBY(group, ABS(changes), VSTACK(MAX, MIN), , 0, -1, changes)
  )

An alternative layout would be achieved using HSTACK(MAX, MIN).

 

[AliGW]

GROUPBY is still only available to Office Insiders, I believe.

 

[Peter Bartholomew]

GROUPBY is still only available to Office Insiders, I believe.

True. In many instances the GROUPBY and PIVOTBY functions will allow the concept of pivot tables to be replaced by standard Excel functions that do not require the use of refresh, provided calculation is not set to manual. My interest in this particular case was that it required the zero values to be filtered out to prevent them appearing in the calculated results. I had never used the filter within GROUPBY so this provided an opportunity. Here 'changes' as the final parameter acts as a Boolean filter.

 

[adamuce]

Let us know.

Hi. Sorry for the late response but I had to go into hospital for an emergency operation on my calf. Came back to work last week.

Thanks again for your support.

I have info and two columns to find the highest and lowest decline. The MINIFS seems to have worked. The MAXIFS comes up wit "0" as the answer

 

[AliGW]

Did you write it correctly?

=MINIFS(B1:B8,B1:B8,"<"&0)
=MAXIFS(B1:B8,B1:B8,">"&0)