How do you write a conditional statement that depends only on part of a field?

[cljordan]

I am trying to create a database that list materials and their quantities, and highlights quantities that come up outside a certain percentage range against the average. I have tried the following conditional formatting:

Condition 1: [quantity]>Avg([quantity])*1.5;
Condition 2: [quantity]<Avg([quantity])*0.5

However, there are a lot of different materials that differ in quantity, and what the report is doing is averaging all of the quantities instead of just the ones under each material.

Is it possible to create a conditional statement where the average differs under each material subgroup?