Array functions & TREND
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
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)}.
