Max and Min number within a column text resulting from formulas

[jeremypyle]

I have tried for ages to work this out. I am trying to find the max number and the min number within a column of formulas. The text result of the formulas are as follows:

d2=#N/A
d3=#N/A
d4=733.33%
d5=-#N/A
d6=#N/A
d7=83.33%
d8=28.57%

In cell e2, I want to search for the maximum number within d2:d8.

#N/A is the result of a formula

In cell e3, I want to search for the maximum number within d2:d8 (less the result of e2. So in effect, it would give me the 2nd greatest percentage.

I'd appreciate somebody's help =)

 

[Aladin Akyurek]

I have tried for ages to work this out. I am trying to find the max number and the min number within a column of formulas. The text result of the formulas are as follows:

d2=#N/A
d3=#N/A
d4=733.33%
d5=-#N/A
d6=#N/A
d7=83.33%
d8=28.57%

In cell e2, I want to search for the maximum number within d2:d8.

#N/A is the result of a formula

In cell e3, I want to search for the maximum number within d2:d8 (less the result of e2. So in effect, it would give me the 2nd greatest percentage.

I'd appreciate somebody's help =)

Control+shift+enter, not just enter:

=MAX(IF(ISNUMBER(D2:D8),D2:D8))

Replace MAX with MIN to obtain the minimum.

 

[jeremypyle]

Control+shift+enter, not just enter:

=MAX(IF(ISNUMBER(D2:D8),D2:D8))

Replace MAX with MIN to obtain the minimum.

Ok thanks so much for your help. That is perfect. How though do you find the max of d2:d8 less the result from cell e2.

For example, e2 will give the greatest result. Then I want e3 to say =MAX(IF(ISNUMBER(D2:D8),D2:D8)less the result of e2

Is this possible?

 

[jeremypyle]

Don't worry, I have figured it out.

=LARGE(IF(ISNUMBER($D$2:$D$2000),($D$2:$D$2000)),1)

gives the largest result

=LARGE(IF(ISNUMBER($D$2:$D$2000),($D$2:$D$2000)),2)

gives the 2nd largest result.

You can do the same with smallest by exchanging LARGE with SMALL

 

[Aladin Akyurek]

Ok thanks so much for your help. That is perfect. How though do you find the max of d2:d8 less the result from cell e2.

For example, e2 will give the greatest result. Then I want e3 to say =MAX(IF(ISNUMBER(D2:D8),D2:D8)less the result of e2

Is this possible?

Don't worry, I have figured it out.

=LARGE(IF(ISNUMBER($D$2:$D$2000),($D$2:$D$2000)),1)

gives the largest result

=LARGE(IF(ISNUMBER($D$2:$D$2000),($D$2:$D$2000)),2)

gives the 2nd largest result.

You can do the same with smallest by exchanging LARGE with SMALL

You are welcome. Thanks for providing feedback.