Value furthest from zero between cell range

Hi back again

In the attached sheet I'm able to find the max and min value between the cell range, but I'm having trouble with finding the correct way to return the value furthest from zero.

I'd like to be able to return the value furthest from zero in column Q to J42, within the times specified in A41 and A43.

I'm using
=MAX(IF((P$3:P$707>=A41)*(P$3:P$707<A43),Q$3:Q$707))

and

=MIN(IF((P$3:P$707>=A41)*(P$3:P$707<A43),Q$3:Q$707))

for the max and min as positive and negative value.
Book.xlsx

Thanks in advance

Pete

Hello pete try this in L42
=MIN(IF((P$3:P$707>=A41)*(P$3:P$707<A43),IF(Q$3:Q$707>0,Q$3:Q$707))) array entered!

Sry Misunderstood
=MIN(IF((P$3:P$707>=A41)*(P$3:P$707<A43)*(Q$3:Q$707<0),Q$3:Q$707)) Try this!

Does this help ?

Please Login or Register  to view this content.

With the formulas in K42 and L42, you could simply put =MAX(ABS(K42),abs(L42)) in J42.

Helper columns sometimes make things easier. You can hide them if needed

How much nesting you want to do is kind of up to you and your skill at building and debugging large, complex, multi-level functions. I have always preferred to use helper columns/cells, so I haven't developed these skills to the same degree as many of the experts here.

This particular one doesn't look like it should be too difficult to nest K42 and L42 into either Pepe Le Mokko's or my version of J42. Using a principle of substitution, you should be able to take and, where our functions have a reference to K42, simply paste in the formula from K42 (without the {}braces and the equals = sign). Once nested, J42 would also need to be an array function (ctrl-shift-enter).