# max min

#### sheppy72

##### Board Regular
Hi all, Hope once again you can sort out what should be a simple question. I have a sheet with 2 rows of data. In another 2 other rows I have a target values. I would like to find the max value from row 1 from data that is aboe the target value. For the second row of data I would like to find the min value from the data that is above the target value. Will try to show below.

row 1 123 345 342 100 178 453 124
row 2 700 232 500 245 646 157 578

Target value 250
target value 500

So for row 1 I want to find the max value in that row above the target value 250
Row 2 I want the min value above the target vlaue of 500

Hope that makes sense.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Domenic

##### MrExcel MVP
Assuming that A1:G2 contains the data, A5 contains the target value for Row 1, and A6 contains the target value for Row 2, try the following formula that need to be confirmed with CONTROL+SHIFT+ENTER...

=MAX(IF(A1:G1>A5,A1:G1))

and

=MIN(IF(A2:G2>A6,A2:G2))

Hope this helps!

#### Peter_SSs

##### MrExcel MVP, Moderator
What do you want to happen if there are no values above the target in a row? If that will never happen, then this should do for the first answer:
=MAX(A1:G1)

#### sheppy72

##### Board Regular
Back again,
After trying out the first reply it did not work in the case of the MIN. I think I can see what it is doing but can not work out how to get it to do what I want. Basically the formula will look at the row of numbers and compare to the target value. However it seems to be working in a way that as there are values above the target value it then says ok then the min vlaue out of those numbers is " ". However what i really wanted to figure out is. When it looks that the row of numbers and compares to the target value it then only returns the min vlaue aboe the target value. An ideas?

#### Peter_SSs

##### MrExcel MVP, Moderator
I don't understand. Here is your sample data and the formulas suggested by Andrew and me. It seems like you are saying the answer 578 in C6 is wrong. Please confirm that and explain what answer you expected and how you arrived at it.

Excel Workbook
ABCDEFGH
1123345342100178453124
2700232500245646157578
3
4AndrewPeter
5250453453
6500578
7
Max_Min

#### sheppy72

##### Board Regular
Hi Peter,

It was completly my error. I did the stupid thing of not confirming with CTR- SHIFT_ENTER

Many thanks it now works a treat.

Replies
5
Views
151
Replies
34
Views
682
Replies
12
Views
365
Replies
0
Views
280
Replies
5
Views
173

1,191,093
Messages
5,984,635
Members
439,897
Latest member
osman53

### 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.

### Which adblocker are you using?

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

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