Find value minimum & maximum if

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,540
Hello To All,</SPAN></SPAN>

I want to find min And max value of the rows, for example in row 2 cells A2:R2 showed 3 values in green which are 24, 10, & 8 they have 1 below in row 3 which is shown in red so far min or row 2 I need 8 and max of row 2 I need 24, is it possible to find with formula.</SPAN></SPAN>


A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
D</SPAN></SPAN>
E</SPAN></SPAN>
F</SPAN></SPAN>
G</SPAN></SPAN>
H</SPAN></SPAN>
I</SPAN></SPAN>
J</SPAN></SPAN>
K</SPAN></SPAN>
L</SPAN></SPAN>
M</SPAN></SPAN>
N</SPAN></SPAN>
O</SPAN></SPAN>
P</SPAN></SPAN>
Q</SPAN></SPAN>
R</SPAN></SPAN>
S</SPAN></SPAN>
T</SPAN></SPAN>
U</SPAN></SPAN>
1</SPAN></SPAN>
Datas</SPAN></SPAN>
Empty</SPAN>
Min</SPAN></SPAN>
Max</SPAN>
2</SPAN></SPAN>
4</SPAN></SPAN>
24</SPAN></SPAN>
9</SPAN></SPAN>
5</SPAN></SPAN>
13</SPAN></SPAN>
13</SPAN></SPAN>
15</SPAN></SPAN>
1</SPAN>
7</SPAN></SPAN>
10</SPAN></SPAN>
3</SPAN></SPAN>
9</SPAN></SPAN>
12</SPAN></SPAN>
16</SPAN></SPAN>
7</SPAN></SPAN>
8</SPAN></SPAN>
5</SPAN></SPAN>
4</SPAN></SPAN>

8</SPAN></SPAN>
24</SPAN></SPAN>
3</SPAN></SPAN>
5</SPAN></SPAN>
1</SPAN>
10</SPAN></SPAN>
6</SPAN></SPAN>
14</SPAN></SPAN>
14</SPAN></SPAN>
16</SPAN></SPAN>
2</SPAN></SPAN>
8</SPAN></SPAN>
1</SPAN>
4</SPAN></SPAN>
10</SPAN></SPAN>
2</SPAN></SPAN>
17</SPAN></SPAN>
8</SPAN></SPAN>
1</SPAN>
6</SPAN></SPAN>
5</SPAN></SPAN>

10</SPAN></SPAN>
14</SPAN></SPAN>
4</SPAN></SPAN>
6</SPAN></SPAN>
26</SPAN></SPAN>
1</SPAN>
7</SPAN></SPAN>
15</SPAN></SPAN>
1</SPAN>
17</SPAN></SPAN>
3</SPAN></SPAN>
9</SPAN></SPAN>
2</SPAN></SPAN>
5</SPAN></SPAN>
11</SPAN></SPAN>
3</SPAN></SPAN>
18</SPAN></SPAN>
9</SPAN></SPAN>
10</SPAN></SPAN>
7</SPAN></SPAN>
6</SPAN></SPAN>

3</SPAN></SPAN>
9</SPAN></SPAN>
5</SPAN></SPAN>
7</SPAN></SPAN>
27</SPAN></SPAN>
2</SPAN></SPAN>
8</SPAN></SPAN>
16</SPAN></SPAN>
2</SPAN></SPAN>
18</SPAN></SPAN>
1</SPAN>
1</SPAN>
3</SPAN></SPAN>
6</SPAN></SPAN>
12</SPAN></SPAN>
4</SPAN></SPAN>
19</SPAN></SPAN>
10</SPAN></SPAN>
11</SPAN></SPAN>
8</SPAN></SPAN>
7</SPAN></SPAN>

7</SPAN></SPAN>
12</SPAN></SPAN>
6</SPAN></SPAN>
1</SPAN>
28</SPAN></SPAN>
3</SPAN></SPAN>
9</SPAN></SPAN>
17</SPAN></SPAN>
3</SPAN></SPAN>
19</SPAN></SPAN>
2</SPAN></SPAN>
2</SPAN></SPAN>
4</SPAN></SPAN>
7</SPAN></SPAN>
1</SPAN>
5</SPAN></SPAN>
20</SPAN></SPAN>
11</SPAN></SPAN>
12</SPAN></SPAN>
9</SPAN></SPAN>
1</SPAN>

9</SPAN></SPAN>
17</SPAN></SPAN>
7</SPAN></SPAN>
9</SPAN></SPAN>
29</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN>
1</SPAN>
4</SPAN></SPAN>
20</SPAN></SPAN>
3</SPAN></SPAN>
3</SPAN></SPAN>
5</SPAN></SPAN>
8</SPAN></SPAN>
14</SPAN></SPAN>
6</SPAN></SPAN>
21</SPAN></SPAN>
1</SPAN>
13</SPAN></SPAN>
10</SPAN></SPAN>
9</SPAN></SPAN>

1</SPAN></SPAN>
10</SPAN></SPAN>
8</SPAN></SPAN>
6</SPAN></SPAN>
5</SPAN></SPAN>
7</SPAN></SPAN>
9</SPAN></SPAN>
9</SPAN></SPAN>
12</SPAN></SPAN>
28</SPAN></SPAN>
1</SPAN>
11</SPAN></SPAN>
13</SPAN></SPAN>
5</SPAN></SPAN>
2</SPAN></SPAN>
4</SPAN></SPAN>
29</SPAN></SPAN>
1</SPAN>
3</SPAN></SPAN>
1</SPAN>
17</SPAN></SPAN>

5</SPAN></SPAN>
29</SPAN></SPAN>
9</SPAN></SPAN>
7</SPAN></SPAN>
1</SPAN>
8</SPAN></SPAN>
1</SPAN>
10</SPAN></SPAN>
13</SPAN></SPAN>
29</SPAN></SPAN>
2</SPAN></SPAN>
12</SPAN></SPAN>
14</SPAN></SPAN>
6</SPAN></SPAN>
3</SPAN></SPAN>
5</SPAN></SPAN>
1</SPAN>
2</SPAN></SPAN>
4</SPAN></SPAN>
2</SPAN></SPAN>
18</SPAN></SPAN>

5</SPAN></SPAN>
29</SPAN></SPAN>
10</SPAN></SPAN>
8</SPAN></SPAN>
2</SPAN></SPAN>
9</SPAN></SPAN>
2</SPAN></SPAN>
11</SPAN></SPAN>
14</SPAN></SPAN>
1</SPAN>
3</SPAN></SPAN>
13</SPAN></SPAN>
15</SPAN></SPAN>
7</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN>
2</SPAN></SPAN>
3</SPAN></SPAN>
5</SPAN></SPAN>
3</SPAN></SPAN>
19</SPAN></SPAN>

7</SPAN></SPAN>
11</SPAN></SPAN>
11</SPAN></SPAN>
9</SPAN></SPAN>
3</SPAN></SPAN>
10</SPAN></SPAN>
3</SPAN></SPAN>
1</SPAN>
15</SPAN></SPAN>
31</SPAN></SPAN>
4</SPAN></SPAN>
14</SPAN></SPAN>
16</SPAN></SPAN>
1</SPAN>
5</SPAN></SPAN>
2</SPAN></SPAN>
3</SPAN></SPAN>
4</SPAN></SPAN>
6</SPAN></SPAN>
4</SPAN></SPAN>
20</SPAN></SPAN>

4</SPAN></SPAN>
10</SPAN></SPAN>
12</SPAN></SPAN>
10</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN>
4</SPAN></SPAN>
2</SPAN></SPAN>
16</SPAN></SPAN>
32</SPAN></SPAN>
12</SPAN></SPAN>
15</SPAN></SPAN>
17</SPAN></SPAN>
2</SPAN></SPAN>
6</SPAN></SPAN>
3</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN>
7</SPAN></SPAN>
1</SPAN>
21</SPAN></SPAN>




<TBODY>
</TBODY>

Please help</SPAN></SPAN>

Thanks In Advance,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,540
Hello To All,</SPAN></SPAN>

This is my attempt to post data in there original format.</SPAN>
ABCDEFGHIJKLMNOPQRSTU
1DatasEmptyMinMax
24249513131517103912167854 824
3511061414162814102178165 1014
4626171511739251131891076 39
57272816218113612419101187 712
6128391731922471520111291 917
7929411420335814621113109 110
8657991228111135242913117 529
9718110132921214635124218 529
1082921114131315741235319 711
11931031153141416152346420 410
121041421632121517263417121

<TBODY>
</TBODY>

</SPAN>

Thanks In Advance,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Sheet1

*ABCDEFGHIJKLMNOPQRSTU
1Datas Empty *****************Min Max
24249513131517103912167854*824
3511061414162814102178165*1014
4626171511739251131891076*39
57272816218113612419101187*712
6128391731922471520111291*917
7929411420335814621113109*110
8657991228111135242913117*529
9718110132921214635124218*529
1082921114131315741235319*711
11931031153141416152346420*410
121041421632121517263417121***

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
T2{=MIN(IF($A3:$R3=1,$A2:$R2))}
U2{=MAX(IF($A3:$R3=1,$A2:$R2))}
T3{=MIN(IF($A4:$R4=1,$A3:$R3))}
U3{=MAX(IF($A4:$R4=1,$A3:$R3))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,540
Thank you Robert Mika, for your help formula is giving ideal result.<o:p></o:p>
Regards,<o:p></o:p>
Kishan<o:p></o:p>
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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?

Disable AdBlock

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
Top