I have data which has the following structu
department, price, type of price

I have raw data already sorted on the basis of the department.
then i copied the entire data to another sheet.
I need to calculate the average of the price, based on specific type
of price

I tried doing a nested subtotal on the new sheet, by department first,
followed by the type of price, averaging on the price field, but im
not sure if the data would auto-update.

I tried pivot table too, but it doesnt show the duplicates, which i
want.

Is there a way out?
i need the new sheet to be dynamic, which automatically updates itself
as new data is entered in the old raw data sheet