Daverage Criteria Not Recognized

[Dhood15277]

Hi, Searched many threads and not yet found an answer. Still using Office 97 Pro so hope someone can remember! When I set up a daverage formula (using this for first time), the criteria range is completely ignored and the formula simply averages the entire field. I thought maybe I needed to install an add-in or something but couldn't find anything. I reinstalled the entire program and now getting a #VALUE!.

The Database is a1:j107, Field is Open, and Criteria is q4:s6. All these ranges are defined names. I'm trying to average all open short stock trades, that is, description is the stock symbol, size is less than 0, and Close price is 0. Criteria keeps coming up as #value! in the daverage help function screen.

The Daverage formula is entered in cell Q8. Cell Q1:R1 simply shows what the result should be. I've tried rearranging the cells so Criteria cells and database cells are all in the same order, and checked the formatting in each cell as appropriate, to no avail. Entering 0's in the Close column does not help so I didn't think its a formatting error..

Thanks in advance for any suggestions. (Not ready to upgrade until next year!)

Dan

<TABLE borderColor=#000000 cellSpacing=0 cellPadding=2 width=1349 border=1><TBODY><TR><TD width="5%" height=16>

Date​

​

​

​

</TD><TD width="5%" height=16>

Acct​

​

​

​

</TD><TD width="7%" height=16>

Desc​

​

​

​

</TD><TD width="5%" height=16>

Size​

​

​

​

</TD><TD width="5%" height=16>

Open​

​

​

​

</TD><TD width="5%" height=16>

Close/Exp​

​

​

​

</TD><TD width="6%" height=16>

Close​

​

​

​

</TD><TD width="6%" height=16>

Note​

​

​

​

</TD><TD width="6%" height=16>

Comm​

​

​

​

</TD><TD width="5%" height=16>

Last​

​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

P&L​

​

​

</TD><TD width="5%" height=16>

Trade​

​

​

</TD><TD width="5%" bgColor=#c0c0c0 height=16>

17.96​

​

​

</TD><TD width="5%" height=16>

% P&L​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

Sold AVG​

​

​

</TD><TD width="5%" height=16>

17.96​

​

​

</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>

10/21/09​

​

​

</TD><TD width="5%" height=16>

ira​

​

​

</TD><TD width="7%" height=16>

SLT​

​

​

</TD><TD width="5%" height=16>

100.00​

​

​

</TD><TD width="5%" bgColor=#c0c0c0 height=16>

17.96​

​

​

</TD><TD width="5%" height=16>

11/21/09​

​

​

</TD><TD width="6%" height=16>

18.18​

​

​

</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

4.00​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

18.00​

​

​

</TD><TD width="5%" height=16>

1​

​

​

</TD><TD width="5%" height=16>

17.78​

​

​

</TD><TD width="5%" height=16>

1.00%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>

10/21/09​

​

​

</TD><TD width="5%" height=16>

mrgn​

​

​

</TD><TD width="7%" height=16>

SLT​

​

​

</TD><TD width="5%" height=16>

-100.00​

​

​

</TD><TD width="5%" height=16>

17.96​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

2.00​

​

​

</TD><TD width="5%" height=16>

18.31​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

(37.00)​

​

​

</TD><TD width="5%" height=16>

2​

​

​

</TD><TD width="5%" height=16>

18.15​

​

​

</TD><TD width="5%" height=16>

-1.06%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>

10/21/09​

​

​

</TD><TD width="5%" height=16>

mrgn​

​

​

</TD><TD width="7%" height=16>

NOV 09 17.5P​

​

​

</TD><TD width="5%" height=16>

-100.00​

​

​

</TD><TD width="5%" height=16>

0.89​

​

​

</TD><TD width="5%" height=16>

11/21/09​

​

​

</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

Expired​

​

​

</TD><TD width="6%" height=16>

2.95​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

86.05​

​

​

</TD><TD width="5%" height=16>

3​

​

​

</TD><TD width="5%" height=16>

17.29​

​

​

</TD><TD width="5%" height=16>

3.73%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

Desc​

​

​

​

</TD><TD width="5%" height=16>

Size​

​

​

​

</TD><TD width="5%" height=16>

Close​

​

​

​

</TD></TR><TR><TD width="5%" height=16>

10/21/09​

​

​

</TD><TD width="5%" height=16>

ira​

​

​

</TD><TD width="7%" height=16>

NOV 09 17.5C​

​

​

</TD><TD width="5%" height=16>

-100.00​

​

​

</TD><TD width="5%" height=16>

1.30​

​

​

</TD><TD width="5%" height=16>

11/21/09​

​

​

</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

Expired​

​

​

</TD><TD width="6%" height=16>

2.95​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

127.05​

​

​

</TD><TD width="5%" height=16>

4​

​

​

</TD><TD width="5%" height=16>

16.02​

​

​

</TD><TD width="5%" height=16>

10.81%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

SLT​

​

​

</TD><TD width="5%" height=16>

<0​

​

​

</TD><TD width="5%" height=16>

0.00​

​

​

</TD></TR><TR><TD width="5%" height=16>

11/23/09​

​

​

</TD><TD width="5%" height=16>

ira​

​

​

</TD><TD width="7%" height=16>

SLT​

​

​

</TD><TD width="5%" height=16>

100.00​

​

​

</TD><TD width="5%" height=16>

18.82​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

1.00​

​

​

</TD><TD width="5%" height=16>

18.31​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

(51.99)​

​

​

</TD><TD width="5%" height=16>

5​

​

​

</TD><TD width="5%" height=16>

16.5389​

​

​

</TD><TD width="5%" height=16>

7.91%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>

11/23/09​

​

​

</TD><TD width="5%" height=16>

ira​

​

​

</TD><TD width="7%" height=16>

SLT​

​

​

</TD><TD width="5%" height=16>

100.00​

​

​

</TD><TD width="5%" height=16>

18.82​

​

​

</TD><TD width="5%" height=16>

11/23/09​

​

​

</TD><TD width="6%" height=16>

18.69​

​

​

</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>

3.00​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

(15.98)​

​

​

</TD><TD width="5%" height=16>

6​

​

​

</TD><TD width="5%" height=16>

16.6987​

​

​

</TD><TD width="5%" height=16>

7.02%​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="7%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>

#VALUE!​

​

​

</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="7%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="6%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR></TBODY></TABLE>

 

[BrianB]

Your criteria range contains an empty row which matches "everything" no matter what else you include.

 

[Dhood15277]

Brian, Thanks for your response. After changing the criteria range to Q4:S5 I get a #DIV/0! which I thought means there are no cells that satisfy the criteria. If I include one or more blank rows under the criteria row I get the answer 12.62497 which is the average of the entire field. I thought this means all cells are selected, as you suggest all cells meet the criteria. Before when I posted, I was getting the #VALUE error because I didn't put double quotations around the Field label in the formula.

Here are the actual entries in the criteria cells (as what is displayed is not what shows on screen): Size: ="SLT" Size: <0 Close/Exp: =0

I've been changing the field names and criteria values to see if there is some kind of invalid formatting or entry type but still no luck.

 

[Dhood15277]

Your criteria range contains an empty row which matches "everything" no matter what else you include.

A little more messing around and it worked. This time I simply copied and pasted the list labels to the criteria labels, instead of typing them in separately. So there must have been a formatting difference between the two. Now all works great. Thanks for your help.