Posted by Jennifer on January 31, 2001 11:50 AM

Setup:

Col A Col B

Row 1 90 4

Row 2 73 3

Row 3 58 2

Row 4 31 1

Row 5 63

The '63' in row 5, col A is a formula - =average(A1:A4)

row 5, col B is blank

The following formulas work:

{=AVERAGE(IF(B1:B4>=1,A1:A4))} equals 63

{=AVERAGE(IF(B1:B4>=2,A1:A4))} equals 73.67

{=AVERAGE(IF(B1:B5>=1,A1:A5))} equals 63

{=AVERAGE(IF(B1:B5>=2,A1:A5))} equals 73.67

The SUM function also works in place of AVERAGE.

{=TREND(IF(B1:B4>=1,A1:A4))} works too, equals 91.8

Why don't the following work??

And how do I make them work??

{=TREND(IF(B1:B4>=2,A1:A4))}

{=TREND(IF(B1:B5>=1,A1:A5))}

Posted by Mark W. on January 31, 2001 12:04 PM

Jennifer, take a look a the help topic for TREND

worksheet function. The argument that you're using

doesn't conform to those expected by the function.

Posted by Mark W. on January 31, 2001 12:23 PM

BTW, what is it that you're trying to do?

Posted by Jennifer on January 31, 2001 12:24 PM

{=TREND(IF(B1:B4>=1,A1:A4))} works! It likes the argument in the formula and spits out an answer of 91.8.

{=TREND(IF(B1:B5>=1,A1:A5))} should pass the TREND function the SAME ARGUMENT - those cells in A1:A5 that correspond with values greater than or equal to 1 in B1:B5 - right? That would be A1:A4, since B5 is not >=1, then A5 should not get passed to the TREND function - and the trend function should only get A1:A4.

Posted by Jennifer on January 31, 2001 12:27 PM

To get the TREND function to work over blanks.

i.e. only name one range - A7:A56 vs. (A7:A10 ; A13:A16 ; A19:A22 ; ...etc,)

Posted by Mark W. on January 31, 2001 12:34 PM

Are you tinkering or trying to develop a solution

to a specific business, scientific or academic

problem? Give us some background.

Posted by Jennifer on January 31, 2001 12:48 PM

I'm working on loss development for auto insurance, same reports every quarter. The spreadsheet has 4 rows of quarters, than a row with the annual total, then a blank row, then on to the next year. We already have a 'counter' running up the side of these - 1 being the most recent and least developed quarter, 2 being last quarter, 3 the quarter before and so on. I wanted to use the array function to 'weed out' only those quarters sufficiently developed to trend on - say with a counter of 10 or higher.

It is also important to be able to name the ENTIRE range so that in following years when I add rows the formula will still work with out editing the arguments.

Posted by Tim Francis-Wright on January 31, 2001 1:46 PM

The problem here is the TREND(IF(... paradigm.

When the result of IF for a cell is FALSE,

TREND panics and produces an error...

I suggest using a name range here.

Set up the following: a cell named MyMax

with the maximum value, a cell named MyMin

with the minimum value, the top cell in the

data in column A, named Top, and its value

in column B, named TopValue.

Now, insert a named called Myrange, defined as:

=OFFSET(Top,MAX(0,TopValue-MyMax),0,MIN(TopValue,MyMax)-MyMin+1,1)

Now, you can use =TREND(MyRange)

Hope this helps!

Posted by Mark W. on January 31, 2001 1:57 PM

When you say,

{=TREND(IF(B1:B4>=1,A1:A4))} works too, equals 91.8

What is it that you believe 91.8 represents?

Posted by Jennifer on January 31, 2001 2:29 PM

I think 91.8 is the y value corresponding to x=1 in the strait line fit to the data (A1:A4 - 90, 73, 58, 31 - known y values and 1, 2, 3, 4 - known x values) using the method of least squares.

Posted by Mark W. on January 31, 2001 2:50 PM

Yes, but in the formulation you're using there aren't

any "known X-values". So the 1, 2, 3, 4 is merely

a substituted ordinal value that's unrelated to your

column B values.

Another question. Why don't you sidestep your

discontiguous range problem altogether by moving

your AVERAGE() results (as currently displayed in

cell A5) to a separate column and adding new quarterly

results in rows directly above existing data? You

could label column A by entering something like

"Value" in cell A1, then in another column (say,

column C) you could select all cells corresponding

to values in column A (say, C2:C5) and enter a

single array formula, {=TREND(Value)}.