calculate average (blank vs zero)

[workhorse]

Currently using a lot of Power Pivot Tables which in part calculate "averages" - but when the data comes form a Query that contains a calculated field using the iff() funtion such as: Outcome: iff(Status = "P", 1,0) we end up with a lot of zeros that create a problem in calcuating a true average. In the following example 2,3,0,1,0,0 the average is "1" but if the zeros were blank the average would be "2" because the blank cells would not be counted.

Is ther a way to have something like: Outcome: iff(status = "P",1,"blank") but what I want is ether 1 or a blank cell, NOT TEXT. If we use "" for the false outcome we still have cells that look blank but are in fact text cells and are counted in calculating average.

Any help would be appreciated,

Workhorse

 

[CJ_London]

Depending on the outcome you require you can try:

SELECT Avg(iif(Status = "P", 1,0)) as Outcome FROM myTable WHERE iif(Status = "P", 1,0)<>0

Using your example will produce 2

 

[workhorse]

Looking at your response are you suggesting that within the query I include the criteria <>0 for the calculated field which I called "Outcome" in my example? If so, would that not eliminated a great deal of records in which I need data from other fields?

I thank you for taking the time to respond to my question - I'm just having a hard time understanding how I should proceed. Sorry for being slow on this.

 

[CJ_London]

No problem. In terms of calculating the average, yes it ignores the other fields - but to get the result you want, it needs to. Perhaps your sample data needs to be more complex to understand better - you could put the code into a subquery which may solve your problem - e.g.

Select ID, Name, (SELECT Avg(iif(Status = "P", 1,0)) FROM myTable as tmp WHERE Name=myTable.name and iif(Status = "P", 1,0)<>0) as Outcome FROM MyTable;

Best way to proceed is to give it a go!

 

[Brianwarnock]

I don't understand why the op doesn't just have a string of 1 and o were do the 2 an 3 come from? However his problem can be overcome by
Avg(Iif(status ="p",1,NULL))

Brian

 

[CJ_London]

good point!

 

[workhorse]

Thanks CJ London - I have been able to work things out.

Regarding the suggestion to use NULL - at least with Power Pivot Tables you can not calculate average when a "NULL" cell comes into play. Blank cells are not counted in the computtion of average.

Thanks for the input - Workhorse