Conditional average?

[Paul B]

I have the formula =SUM(IF(MONTH(gas!$A$5:$A$200)=B$1,IF(YEAR(gas!$A$5:$A$200)=$A4,gas!$E$5:$E$200,0))) in B4 and filled across to M4, I have =SUM(B4:M4) in N4 to give me the totals, all of this works fine, I want to get the average, but if I use = AVERAGE(B4:M4) it averages all 12 columns even if there is no data in them yet, is there a way to average only the columns that have data in them?

 

[VoG]

This function will average only cells containing numeric data

Function AVG(Data As Range)
Dim nVal As Integer, iCell As Object
AVG = 0
nVal = 0
For Each iCell In Data
If iCell.Value <> "" Then
If IsNumeric(iCell.Value) Then
AVG = AVG + iCell.Value
nVal = nVal + 1
End If
End If
Next iCell
If nVal > 0 Then
AVG = AVG / nVal
Else
AVG = [#N/A]
End If
End Function

 

[Paul B]

Vog, that still gives the avg for all the columns?
This message was edited by Paul B on 2002-11-03 10:20

 

[dk]

An alternative to Vog's solution is to use this:-

=AVERAGE(IF(B4:M4<>0,B4:M4))

REMEMBER - This is an array formula - you enter it by pressing Ctrl+Shift+Enter, not just enter.

 

[Aladin Akyurek]

On 2002-11-03 09:18, Paul B wrote:
I have the formula =SUM(IF(MONTH(gas!$A$5:$A$200)=B$1,IF(YEAR(gas!$A$5:$A$200)=$A4,gas!$E$5:$E$200,0))) in B4 and filled across to M4, I have =SUM(B4:M4) in N4 to give me the totals, all of this works fine, I want to get the average, but if I use = AVERAGE(B4:M4) it averages all 12 columns even if there is no data in them yet, is there a way to average only the columns that have data in them?

That requires changing your multiconditional formula in B4 either to array-entered...

=IF(COUNT(gas!$A$5:$A$200),SUM(IF(MONTH(gas!$A$5:$A$200)=B$1,IF(YEAR(gas!$A$5:$A$200)=$A4,gas!$E$5:$E$200,0))),"")

or, alternatively, to normally entered...

=IF(COUNT(gas!$A$5:$A$200),SUMPRODUCT((MONTH(gas!$A$5:$A$200)=B$1)*(YEAR(gas!$A$5:$A$200)=$A4),gas!$E$5:$E$200),"")

Now, ordinary AVERAGE should work as intended.

 

[Paul B]

Dk, that works, thanks
Aladin, I tried both of yours but the average came back as the 12 columns?

 

[plettieri]

Hi You might try this....

formular samples.xls
	C	D	E	F	G	H	I	J	K	L	M
36
37				100	100		100		100	100
38
39	average with blanks not counted
40
41							100
42
miller-avg

hope this helps...

pll

 

[Aladin Akyurek]

On 2002-11-03 10:25, Paul B wrote:
Dk, that works, thanks
Aladin, I tried both of yours but the average came back as the 12 columns?

Not so fast. I need some clarification here. As I understood it, you're doing a multiconditional sum wrt to the worksheet gas in B4:M4 in a separate worksheet. The original array-formula will give you a 0 when there is no data in gas. This kind of 0's will distort the ordinary average...

=AVERAGE(B4:M4)

So, I proposed to modify the multiconditional formula not to produce 0's but "" (blanks). After this modification, the result of...

=AVERAGE(B4:M4)

will not be based on unjustified 0's. Other proposals eliminates all 0's, justified and unjustified.
This message was edited by Aladin Akyurek on 2002-11-03 12:17

 

[Paul B]

Aladin, I tired changing the 0 to blank before I posted, I thought that would work, but it did not, I am testing to match the data in “gas” column A with the month and year in anther sheet, then if they match sum the amount in “gas” column E. like I said in the beginning I thought the blank would have fix it. I would be happy to send you an example of the workbook if you would like, so you could show me why I can’t get it to work, just let me know. Thanks again Paul B pab@surfbest.net

 

[Aladin Akyurek]

On 2002-11-03 12:12, Paul B wrote:
Aladin, I tired changing the 0 to blank before I posted, I thought that would work, but it did not, I am testing to match the data in “gas” column A with the month and year in anther sheet, then if they match sum the amount in “gas” column E. like I said in the beginning I thought the blank would have fix it. I would be happy to send you an example of the workbook if you would like, so you could show me why I can’t get it to work, just let me know. Thanks again Paul B pab@surfbest.net

After seeing your data, I'm convinced that the array formula that you are using cannot produce a true 0 dollar amount. In order to avoid averaging with spurious 0's, you can just use...

=SUM(B4:M4)/MAX(1,COUNT(B4:M4)-COUNTIF(B4:M4,0))

By the way, defining a single dynamic name (e.g., GData) for your gas data, you can use

=SUMPRODUCT((MONTH(INDEX(GData,0,1))=B$1)*(YEAR(INDEX(GData,0,1))=$A4),INDEX(GData,0,5))

instead of the array-entered formula.