Daverage Criteria Not Recognized

Dhood15277

New Member
Joined
Dec 3, 2009
Messages
3
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>
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Your criteria range contains an empty row which matches "everything" no matter what else you include.
 

Dhood15277

New Member
Joined
Dec 3, 2009
Messages
3
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

New Member
Joined
Dec 3, 2009
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,798
Messages
5,598,112
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top