Max & Min of the column

[jinroh]

Hi,

I have a example set of data and I would like to give an output for Port 1, the Max and Min value, Port 2, the Max and Min value and etc ....

Other than setting is manully the range, Is there a method to grab the Real Power value based on Port 1 into an array and do a max on the array range ?
Thanks

PORT------ Real Power
1 --------- 7.6
1 --------- 5.5
1 --------- 4.1
1 --------- 3.4
1 --------- 4.8
2 --------- 4.5
2 --------- 3
2 --------- 2
2 --------- 2.6
3 --------- 5.6
3 --------- 2.8
3 --------- 3.8
3 --------- 4.1
3 --------- 3.9
4 --------- 5.9
4 --------- 4.7
4 --------- 3.8
4 --------- 3.5
4 --------- 2.9
4 --------- 4

[Azam Ali]

Use array formula and confirm though Ctrl+Shift+Enter

=MIN(IF($A$1:$A$100=1,$B$1:$B$100))
=MAX(IF($A$1:$A$100=1,$B$1:$B$100))

replace 1 with 2, 3, 4 in the above formula or linked formula with a cell# in which these numbers are mentioned

[TMS]

=MAX(IF($A$2:$A$21=$D2,$B$2:$B$21))

Array Entered with Ctrl-Shift-Enter rather than just Enter.

with 1, 2, 3, 4 in cells D2, D3, D4 and D5 respectively.


Regards

[jinroh]

thanks guy for the formula. one more question;

if there is separate table for each port 1~4 in the same worksheet for example

Same worksheet
Table A
PORT------ Real Power
1 --------- 7.6
1 --------- 5.5
1 --------- 4.1
1 --------- 3.4
1 --------- 4.8
2 --------- 4.5
2 --------- 3
2 --------- 2
2 --------- 2.6
3 --------- 5.6
3 --------- 2.8
3 --------- 3.8
3 --------- 4.1
3 --------- 3.9
4 --------- 5.9
4 --------- 4.7
4 --------- 3.8
4 --------- 3.5
4 --------- 2.9
4 --------- 4

Table B
PORT------ Real Power
1 --------- 7.6
1 --------- 5.5
1 --------- 4.1
2--------- 7.6
2--------- 5.5
2 --------- 4.1
3--------- 7.6
3--------- 5.5
3 --------- 4.1
4--------- 7.6
4--------- 5.5
4 --------- 4.1

I would like to separate the output, if i use the formula, it will take in table B port 1~4, any formula to automatically stopped after it detected a blank space ? between the Table A and Table B is a empty row.

[Azam Ali]

Not clear what you need

Upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button