Conditional average?

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
575
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?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Well-known Member
Joined
Feb 15, 2002
Messages
575
Vog, that still gives the avg for all the columns?
This message was edited by Paul B on 2002-11-03 10:20
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
575
Dk, that works, thanks
Aladin, I tried both of yours but the average came back as the 12 columns?
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

Hi You might try this....
formular samples.xls
CDEFGHIJKLM
36
37100100100100100
38
39average with blanks not counted
40
41100
42
miller-avg


hope this helps...

pll
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Feb 15, 2002
Messages
575
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,550
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top