formula needed... IF, SUMIF, or SUM ???

[carlairis]

Here is my data...
2008 B C
January 1.76 3.95
February 2.70 3.36
March 2.22 3.02
April 2.55 2.81
May 3.11 3.19
June #DIV/0!

I'm working on a semiannual performance. Basically I need to sum-up the
data in column C for Jan, Feb, Mar and so on..and then divide this by 6.
But I will not know the data of June until July. So far, I'm using the IF
function but I still get the DIV#0! error... I need the formula to add up
according to the total of months that I have... For instance, in March I will
only need to add up to 3 data value set and divided by 3, but I don't want to
change the formula everymonth. I want a formula that work for 6 months. I
hope someone can help. Thanks!

 

[Billy Liddel]

Here is my data...
2008 B C
January 1.76 3.95
February 2.70 3.36
March 2.22 3.02
April 2.55 2.81
May 3.11 3.19
June #DIV/0!

I'm working on a semiannual performance. Basically I need to sum-up the
data in column C for Jan, Feb, Mar and so on..and then divide this by 6.
But I will not know the data of June until July. So far, I'm using the IF
function but I still get the DIV#0! error... I need the formula to add up
according to the total of months that I have... For instance, in March I will
only need to add up to 3 data value set and divided by 3, but I don't want to
change the formula everymonth. I want a formula that work for 6 months. I
hope someone can help. Thanks!

You can write a UDF to ignore errors. Try this copied into the books vb module

Function FlexAvg(data) As Double
Dim count As Long, mySum As Double, c
For Each c In data
If IsError(c) Then
mySum = mySum
count = count
ElseIf IsNumeric(c) And c > 0 Then
mySum = mySum + c
count = count + 1
End If
Next
FlexAvg = mySum / count
End Function

Regards
Peter

 

[Roger Govier]

Hi

I am presuming the data to be summed is in column B and the result is in
column C.
If different change the column references accordingly
In C2
=IF(B2="","",SUM(OFFSET(B2,0,0,MIN(6,COUNT($B$1:B2))*-1,1))/MIN(6,COUNT($B$2:B2)))

Copy down as far as required

 

[carlairis]

Thank you for your reply, I think that my post was not clear enough,
I might have confuse you. I created a workbook with reference cells that
have dependencies on other worksheets and/or workbook, until these other
cells have a value placed in them my reference cell returns the #DIV/! error.
Here is a brief example, book1, lets say have two sheets set up one named
Sheet1: wkld (the source) and the other sheet2: PerfReview Jan-June. In
sheet1 I have the following cells:
(cell)month
(a10)January 1.76 3.95
(a11)February 2.70 3.36
(a12)March 2.22` 3.02
(a13)April 2.55 2.81
(a14)May 3.11 3.19
(a15)June #DIV/0!

I want the formula to calculate the average of the current data set for
column C (Column B is something else), even if I have 3 to 5 #DIV/0! Error
cell. Can that be possible?? I tried using AVERAGE fxn, but it's still
giving me error. How can I tell the formula to ignore these? Thanks, again!

 

[Roger Govier]

Hi

Because of the wrapping by the NG reader it is not easy to see the values
you have.
Where do you want the formula to exists? In column D?
If so, just change the formula I gave you to refer to column C instead of
Column B
=IF(C2="","",SUM(OFFSET(C2,0,0,MIN(6,COUNT($C$2:C2))*-1,1))/MIN(6,COUNT($C$2:C2)))

If you still can't resolve the problem, send me a copy of your workbook.

To send direct use
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.

 

[carlairis]

Thanks for your reply... but i figure it out by using this..

=AVERAGEIF(C10:C15,">0",C10:C15)

Basically, it ignores the #DIV/0! that I have reference from another
worksheet. Your response was really impressive...

 

[Billy Liddel]

I could not get your formula to work - I'm using xl2003, however this array
formula worked; enter it as Ctrl + Shift Enter

=AVERAGE(IF(ISNUMBER(C2:C13),C2:C13))

Peter