Help with pivot table

[bakerwe3]

Hi

I have pivot table with 2 years of cost data for services and I have taken average cost as a sum. The issue I have is some of my data has N/A e.g

cars	2019/20	£5000
cars	2018/19	N/A

So when I pivot the above instead of excel taking 5000 it takes the average of two years which is £2500 Is there way I can get pivot to work and ignore N/A?

 

[denzo36]

Not sure what you are trying to do - are you using your pivot to sum up the total for 2 years?

Your pivot won't work as you have text and numbers in your cost column - change the N/A to 0 and it will work as normal

 

[yky]

Not sure what you are trying to do - are you using your pivot to sum up the total for 2 years?

Your pivot won't work as you have text and numbers in your cost column - change the N/A to 0 and it will work as normal

Wouldn't that still give an average of 2500, which the OP is trying to avoid?

 

[denzo36]

Still not sure of his/her intention really - if you want to sum or average in a pivot he'd need to remove the N/As no? And isn't the average of the 2 years not 2500?