Issue with DAverage formula

[jackbergersen]

I am trying to use the DAverage formula to get the average price of a product based on certain criteria. However, it seems that DAverage isn’t using an exact match, rather just searching for the presense of that variable criteria in the column. For instance, there are possible values for the criteria: Price/Description/Image, Image/Description, and Price. When I use the following formula:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>=IF(ISERROR(DAVERAGE(Database!A:M,'Benchmarking Tool'!D14,'Benchmarking Tool'!1:2)),"",DAVERAGE(Database!A:M,'Benchmarking Tool'!D14,'Benchmarking Tool'!1:2))
<o> </o>
With the following in the header row for the daverage function:
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>
<o>

</o>
I get the correct average, based on the following data:

<o>

</o>
However, when I put in just ‘price’ in the header row, it returns the average based on rows 2,3,4,5, rather than just the exact matches in rows 4 & 5….what am I doing wrong?
<o> </o>

 

[AnAnalyst]

Hi there,

Try typing the following into cell D2 of your criteria range:

'=Price

 

[jackbergersen]

Awesome- that worked perfectly! Strange- so just putting the value in there functions much like a SQL 'Contains' statement, whereas the '=value' serves as an exact qualifier?

 

[AnAnalyst]

Absolutely !!

You might also be interested in the following link which explains in a lot more depth all of the options available to you with this type of function:

http://office.microsoft.com/en-us/excel-help/daverage-HP005209045.aspx