AverageA function in Pivot Table - is it possible?

[Tashat]

Hi all

I'm using Excel 365 and wondered if there is a way to use the AverageA function in a pivot table? I want to calculate the true average, but some of my cells contain text and I want them included as part of the average calculation. Is this possible?

Thanks in advance

Natasha

 

[RoryA]

I'd suggest you add a column to your source data to return 0 for text values or the number value otherwise, then use that as your Average column.

 

[Tashat]

I'd suggest you add a column to your source data to return 0 for text values or the number value otherwise, then use that as your Average column.

OK thank you. Yes I did think of that, but was hoping not to have to do that. Thank you though.

 

[RoryA]

It doesn't have to be in the source data per se - if you're using the data model or a query you could do it there. You could also use a simple SUM/COUNT measure in the data model without a new column.