PivotTable Conditional Average

Question

I created a standard pivot table with multiple row, one of which is an average calculation.

I was wondering how I'd modify it so that it calculate the average, but only if there is a value >0.  Ignoring all the 0 when it calculates the Average.  Can this be done, how?

QuestionBoy

Answer 1

I made a whole lot of assumptions that would affect

the method and results of the solution.

To make a better fit please specify:

Is you data a bookkeeping record of financial transactions like mine?

Do you want the average of multiple daily transactions or

the average of the sum of monthly transactions?

Do you enter no transactions as zero or blank or skip the date?

If the daily or monthly transactions add up to zero,

do you ignore the zero?

An upload of a huge sample at MediaFire.com would be helpful.

Answer 2

This appears to be exactly what I need, but would need a little hand holding on this one.  I can't seem to figure out how/where you managed to set it up to ignore the 0s.

How did you go about creating the pivot tables and chart exactly so I can do the same?

Answer 3

Excel 2007/2010 PivotTable

Average the values in a PivotTable, ignoring zeros.

With iterative GetPivotData().

http://c3017412.r12.cf0.rackcdn.com/09_03_11.xlsx

If you get *.zip, don't unzip, just rename *.xlsx

Answer 4

Hi,

let's say that you data are on sheet1, in range A1:A10

in cell A11 you have that you want.

Right click on the Sheet1 tab

Select View code

Paste the code below on the right

Close VB Sheet1 Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Cells) Is Nothing Then

t = 0

s = 0

For i = 1 To 10

If Cells(i, "A") > 0 Then

t = t + 1

s = s + Cells(i, "A")

End If 

Next i

If t = 0 Then 

Range("A11") = 0

MsgBox "There isn't positive number"

Exit Sub

End If 

End If 

Range("A11") = Round(s / t, 2)

End Sub

(you may change: the numbers 1, 10, column A  and cell A11 as needed)