# Max and Min number within a column text resulting from formulas

#### jeremypyle

##### Board Regular
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 =)

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.

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?

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

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.

Replies
10
Views
207
Replies
9
Views
158
Replies
1
Views
798
Replies
4
Views
176
Replies
0
Views
222

1,202,959
Messages
6,052,797
Members
444,602
Latest member
Cookaa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back