MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Array functions & TREND

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

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??

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:

Now, you can use =TREND(MyRange)

Hope this helps!

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

Just curious...

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

Re: Just curious...

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

Re: Just curious...

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)}.