Help with ignoring blank cells in pivot table

[mccabe10]

Hello,

I have a series of daily data for a series of employees and some employees have no data on certain days. Is there a way that I can average the data in a pivot table without the pivot table recognizing the blank cell as a 0? I do not want to assign a value to the blank cell, I want it to be ignored. An example of the data would be:

Date Employee Sales
1/1/17 Employee1 $45
1/1/17 Employee2
1/1/17 Employee3 $17
1/2/17 Employee1
1/2/17 Employee2 $22
1/2/17 Employee3 $14

 

[jorismoerings]

Hi,

If you make proper use of the average function in the Pivottable it should automatically exclude empty fields. As you can see in this example.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	Date	Employee	Sales		Row Labels	Average of Sales
2	1-1-2017	Employee1	45		Employee1	45
3	1-1-2017	Employee2			1-1-2017	45
4	1-1-2017	Employee3	17		2-1-2017
5	2-1-2017	Employee1			Employee2	22
6	2-1-2017	Employee2	22		1-1-2017
7	2-1-2017	Employee3	14		2-1-2017	22
8					Employee3	15,5
9					1-1-2017	17
10					2-1-2017	14
11					Grand Total	24,5
Sheet1