Pivot tables and AVERAGEIF function

[justinsmyth1]

Hi guys,

Newbie to the forums and also fairly new to lookup tables.

I have a sheet of data related to an audit - it is related to meds and weight loss, but there are a lot of other columns of related data. There are a number of drugs, and the weight loss is either a positive or negative number depending if it was lost or gained. I would like to average the positive and negative numbers seperately so I can see what the average loss was if people lost, or the average gain if gained. I was able to do this on the original sheet fairly straightforwardly with the AVERAGEIF function with the criteria ">0" for positive values and "<0" for negative values.

I am more interested in seeing the average weight loss/gain for each of the drugs. For other parameters a straightforward pivot table worked but I cannot see a way to use a pivot table to average only the positive or negative values.

Is there any way of incorporating the AVERAGEIF, or a similar function, into a pivot table, or is there some other way of working around this?

I hope that this is clear and I hope that someone can help - trawling through hundreds of entries manually would not be pleasant!

Thanks,
Justin

P.S. can't upload original file but can make a dummy one if it will help.

[Mandy11]

make a dummy of the file and I will try and help.