help with max, min, <, > in array functions

[JasonV]

I have the following array formula for summing a geometric propert
(surface width) as a function of coordinate pairs and a relativ
elevation:

{=SUM(IF(y+D<MIN(Za,Zb), 0, IF(OR(Za=Zb, y+D>MAX(Za,Zb)), 1
(y+D-MIN(Za,Zb))/ABS(Za-Zb))*ABS(Xa-Xb)))}

Where Za, Xa are paired coordinates, and Zb, Xb refer to the same lis
of paired coordinates, but offset down by one row. y and D ar
constants (depth and datum).

This always returns the wrong result, and I believe that the reason i
the following: The max and min functions are returning the absolut
maximum and minimum values of the lists, and NOT the max / min of th
two values being compared at the indexing of the array formula.

I thus replaced my max/min formulas with explicit if() and and(
functions, as in the following:

{=SUM(IF(AND(y+D<Za,y+D<Zb), 0, IF(OR(Za=Zb, AND(y+D>Za,y+D>Zb)), 1
(y+D-if(Za<Zb,Za,Zb))/ABS(Za-Zb))*ABS(Xa-Xb)))}

This formula results in a different, but still wrong, result. I don'
know what the problem is, but I am guessing that it still has somethin
to do with the relational operators (<, > ) being applied in an arra
formula. (??)

I'm sure that my logic is correct, because when I do the individua
calculations independantly of the array formula, the sum returns th
proper result.

My data is as follows:

X Z
0 100
5 97
10 97.5
15 101

(thus Xa = 0, 5, 10; Xb = 5, 10, 15; Za = 100, 97, 97.5; Zb = 97, 97.5
101)

D = min (Z) = 97
y = 0.25

The correct formula result is 2.917. The results of the two abov
functions are 3.274 and 2.560 respectively.

If y = 1.0 the correct formula result is 7.381. The results of the tw
above functions are 13.095 and 12.381.

If anyone has any suggestions on what I am doing wrong or how to bette
implement this function, please respond.

thanks

 

[BrianB]

In the absence of another reply ...............

Complex formulas can easily be broken down into separate cells to chec
what is being returned by each function, with a summary formula tha
refers to previous cells. This final formula can then be copied an
parts replaced with functions rather than cell references.

I rarely go to this last stage because there is a limit to the formul
length of 256 characters (XL97). It is usually enough to hide th
columns containing the intermediate calculations