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>
</TD><TD width="5%" height=16>
</TD><TD width="5%" height=16>
</TD></TR><TR><TD width="5%" 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></TR><TR><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></TR><TR><TD width="5%" 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="6%" 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></TR><TR><TD width="5%" 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></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></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>
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>