Array formula difficulty

[Sean Cunningham]

I have an array formula which finds a negative average correctly but not a
positive average for a small column of numbers

For example I have a small column containing positive and negative numbers
- formatted as percentages. Column A1:A20

I have manually filled the first 13 cells in the column with numbers and
have no gaps.
For the last 7 cells I have put in a simple formula to copy a value from
another cell if that cell has a value, otherwise to leave the cell blank.
Accordingly the last 7 cells are empty.

Now I would like to find the average of the last 2 negative numbers in the
column and the average of the last 2 positive numbers

To find the average of the last two negative numbers I entered this array
formula - into D5

=SUM(IF($A$1:$A$20<=0,IF(ROW($A$1:$A$20)*($A$1:$A$20<=0)>=LARGE(ROW($A$1:$A$20)*($A$1:$A$20<=0),$C1),($A$1:$A$20),0),0))/$C1

Note $C1 equals "2" - meaning I'm looking at just the average of the last
two numbers.

Now this works just great. No problems

Getting the formula to work for positive numbers is where I have run into
difficulty

For finding the average of the last 2 positive numbers I entered the
following array formula

=SUM(IF($A$1:$A$20>0,IF(ROW($A$1:$A$20)*($A$1:$A$20>0)>=LARGE(ROW($A$1:$A$20)*($A$1:$A$20>0),$C1),($A$1:$A$20),0),0))/$C1

This returns 0% - wrong answer

However when I enter the address of just the occupied cells as in...

=SUM(IF($A$1:$A$13>0,IF(ROW($A$1:$A$13)*($A$1:$A$13>0)>=LARGE(ROW($A$1:$A$13)*($A$1:$A$13>0),$C1),($A$1:$A$13),0),0))/$C1

This gives me the right answer but means I have to manually change the
formula each time a row is filled.

How do I get the formula to work as the negative average works.

I'm using Excel 2000

Thanks for any guidance you can give

 

[Bob Phillips]

Try this

=SUM(IF($A$1:$A$20>0,IF(ROW($A$1:$A$20)*($A$1:$A$20>0)>=LARGE(ROW($A$1:$A$20)*($A$1:$A$20<>"")*($A$1:$A$20>0),$C1),($A$1:$A$20),0),0))/$C1

 

[Sean Cunningham]

Try this

=SUM(IF($A$1:$A$20>0,IF(ROW($A$1:$A$20)*($A$1:$A$20>0)>=LARGE(ROW($A$1:$A$20)*($A$1:$A$20<>"")*($A$1:$A$20>0),$C1),($A$1:$A$20),0),0))/$C1

Thank you Bob - it works wonderfully

 

[Bernd P]

Hello Sean,

Your and Bob's formulas are not returning correct results in case you
have less than C1 positive (or negative) values in A1:A20.

I suggest to use a plain and simple UDF:
Function AvgLast(r As Range, n As Long) As Double
'Returns average of last n positive values in range r
'(if n>0) or last -n negative values (if n<0).
Dim i As Long, lCount As Long, dSum As Double
i = r.Count
Do While i > 1
If Sgn(r(i)) = Sgn(n) Or (r(i) = 0 And n < 0 And r(i) <> "") Then
dSum = dSum + r(i)
lCount = lCount + 1
If lCount = Abs(n) Then Exit Do
End If
i = i - 1
Loop
AvgLast = dSum / lCount
End Function

Press ALT + F11, insert a new module, copy my macro text into that new
module, go back to your worksheet and enter =AvgLast(A1:A20,-2) or
=AvgLast(A1:A20,2), for example.

Regards,
Bernd

 

[JoeU2004]

Your and Bob's formulas are not returning correct
results in case you have less than C1 positive
(or negative) values in A1:A20.

I was thinking the same thing earlier. Your UDF might be the more efficient
solution. But for an Excel alternative, try the following array formulas
(commit with ctrl+shift+Enter instead of Enter):

=IF(COUNTIF(A1:A20,"<=0"),
AVERAGE(IF(ROW(A1:A20)*(A1:A20<=0) >=
LARGE(ROW(A1:A20)*(A1:A20<=0)*(A1:A20<>""),
MIN(C1,COUNTIF(A1:A20,"<=0"))), A1:A20)), 0)

=IF(COUNTIF(A1:A20,">0"),
AVERAGE(IF(ROW(A1:A20)*(A1:A20>0) >=
LARGE(ROW(A1:A20)*(A1:A20>0)*(A1:A20<>""),
MIN(C1,COUNTIF(A1:A20,">0"))), A1:A20)), 0)

Use absolute references as you see fit.

PS: It is not obvious to me why we don't need
IF(ROW(A1:A20)*(A1:A20<=0)*(A1:A20<>""). But Excel 2003 Evaluate Formula
and F9 do not seem to work correctly with this formula, so I cannot see what
is happening. And it does seem to work fine as written above, despite my
attempts to break it.

Note to OP: Your definition of "negative" numbers does not match correct
math usage. (Google "define: positive number" without quotes.) If you
meant "non-positive" numbers, fine. Otherwise, you might want to reconsider
your conditions "<=0" and ">0", based on correct terminology.


----- original messages -----

Hello Sean,

Your and Bob's formulas are not returning correct results in case you
have less than C1 positive (or negative) values in A1:A20.

I suggest to use a plain and simple UDF:
Function AvgLast(r As Range, n As Long) As Double
'Returns average of last n positive values in range r
'(if n>0) or last -n negative values (if n<0).
Dim i As Long, lCount As Long, dSum As Double
i = r.Count
Do While i > 1
If Sgn(r(i)) = Sgn(n) Or (r(i) = 0 And n < 0 And r(i) <> "") Then
dSum = dSum + r(i)
lCount = lCount + 1
If lCount = Abs(n) Then Exit Do
End If
i = i - 1
Loop
AvgLast = dSum / lCount
End Function

Press ALT + F11, insert a new module, copy my macro text into that new
module, go back to your worksheet and enter =AvgLast(A1:A20,-2) or
=AvgLast(A1:A20,2), for example.

Regards,
Bernd

 

[Sean Cunningham]

Yes, I was aware that the value in C1 had to be kept within a limit, but up
until your reply I took it as a given I'd have to manually keep an eye on
it.
I am very grateful to you both for giving me a solution for that.

Sorry for my use of loose language, but you are correct I intended
"negative" numbers to mean "non-positive" numbers.

Thank you all for your fantastic help

 

[Bernd P]

Hello,

Take
Do While i > 0

Regards,
Bernd

 

[Bernd P]

Hello again,

I think if there is no positive (or no non-positive) number at all,
the result should be an error value (=undefined) and not zero.

Regards,
Bernd

 

[Sean Cunningham]

Hi Bernd

I have tried your UDF idea and have pasted your code into a new module.

When I put =AvgLast(A1:A20,2) or =AvgLast(A1:A20,-2) into a cell I get
#Value.
However once I change the argument to only include the cells with numbers
i.e =AvgLast(A1:A13,2) I get the correct answer.

I have'nt used UDFs before so I hope I have not messed anything up.

Is there a way this can work without having to alter the formula every time
an additional cell in the range is populated with a number?

Thank you again for your help

 

[Bernd P]

Hello Sean,

You did alright but I did not take into account that you can encounter
non-numerical strings.

Use this:

Function AvgLast(r As Range, n As Long) As Double
'Returns average of last n positive values in range r
'(if n>0) or last -n negative values (if n<0).
Dim i As Long, lCount As Long, dSum As Double
i = r.Count
Do While i > 0
If IsNumeric(r(i)) Then
If Sgn(r(i)) = Sgn(n) Or (r(i) = 0 And n < 0 And r(i) <> "")
Then
dSum = dSum + r(i)
lCount = lCount + 1
If lCount = Abs(n) Then Exit Do
End If
End If
i = i - 1
Loop
AvgLast = dSum / lCount
End Function

Regards,
Bernd

 

[Sean Cunningham]

Please I must express my thanks to Bob, Joel and of course yourself Bernd
for the great help you've given me in sorting out this problem.

You are all masters.