max min

[sheppy72]

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.

 

[Domenic]

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]

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]

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]

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
	A	B	C	D	E	F	G	H
1	123	345	342	100	178	453	124
2	700	232	500	245	646	157	578
3
4			Andrew	Peter
5	250		453	453
6	500		578
7
Max_Min

 

[sheppy72]

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.