I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

The "D" functions are "too confusing"!

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=AVERAGE(IF((A7:A13=A1)*(A7:A13<=A2),C7:C13))

The result is -3.98 not -3.83.

Format the cell in the negative style of your choice.

Biff

"JR Hester" wrote in message
...
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered
here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

The criteria have to be set up in A1:D2

Date Name Variance Date
=3/1/06 <=3/31/06


It worked okay for me using range references instead of named ranges.


"JR Hester" wrote:

I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

Actually once you get the hang of it they are very efficient albeit
clumsy to set up, they are also much faster than an array formula
if the data set is large. By learning them you'll also learn the
advanced filter which uses the same type of criteria


Peo Sjoblom

T. Valko wrote:
The "D" functions are "too confusing"!

Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=AVERAGE(IF((A7:A13=A1)*(A7:A13<=A2),C7:C13))

The result is -3.98 not -3.83.

Format the cell in the negative style of your choice.

Biff

"JR Hester" wrote in message
...
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered
here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

All you said is true but I find the array formula easier. Or, when the
situation allows, Sumproduct is easier.

Biff

"Peo Sjoblom" wrote in message
...
Actually once you get the hang of it they are very efficient albeit clumsy
to set up, they are also much faster than an array formula
if the data set is large. By learning them you'll also learn the advanced
filter which uses the same type of criteria


Peo Sjoblom

T. Valko wrote:
The "D" functions are "too confusing"!

Try this entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF((A7:A13=A1)*(A7:A13<=A2),C7:C13))

The result is -3.98 not -3.83.

Format the cell in the negative style of your choice.

Biff

"JR Hester" wrote in message
...
I am attempting to use Dsum and Daverage with a date range. Below is
sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered
here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges
to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks to each of you for your ideas, comments and recommendations. I would
agree that a pivot table might be a better fit in that instance. I am trying
to understand the "D" functions, however. I have succeeded in getting a
successful response with single criterion and even with "AND" criteria. That
is when the AND is two separate fields. What I am still having trouble
entering is a "RANGE" of criteria, such as all entries between, say 10/1/06
and 10/31/06. Of course in Access I simply enter = 10/1/06 and <=10/31/06 in
a criteria, but I have not found how to do this in the Excel criterion cells.
Any suggestions? Again I am trying to understand these D functions and their
use.

Thanks

"Debra Dalgleish" wrote:

Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

In the criteria range, you can use two columns for Date as JMB
suggested. Enter both criteria on the same row, which is like using AND.

Date Date
=10/1/06 <=10/1/06

There are other sample criteria he

http://www.contextures.com/xladvfilter01.html#Criteria

http://www.contextures.com/xladvfilter02.html

The examples are for Advanced Filter, but are the same for the Database
functions.

JR Hester wrote:
Thanks to each of you for your ideas, comments and recommendations. I would
agree that a pivot table might be a better fit in that instance. I am trying
to understand the "D" functions, however. I have succeeded in getting a
successful response with single criterion and even with "AND" criteria. That
is when the AND is two separate fields. What I am still having trouble
entering is a "RANGE" of criteria, such as all entries between, say 10/1/06
and 10/31/06. Of course in Access I simply enter = 10/1/06 and <=10/31/06 in
a criteria, but I have not found how to do this in the Excel criterion cells.
Any suggestions? Again I am trying to understand these D functions and their
use.

Thanks

"Debra Dalgleish" wrote:


Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:

I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Use this criteria

Date Date Name Variance
="=3/01/06" ="<=3/31/6"


then I used this formula

=DAVERAGE(Variances, "Variance", A1:B2)

if you want to use 2 input cells (start date in E1 and end date in F1)
and put the dates there you can use

Date Date Name Variance
="="&E1 ="="&E1

it will return the date serial number in the cells which looks a bit
strange

=38777 and <=38807

anyway, that will work fine


Regards,

Peo Sjoblom




JR Hester wrote:
Thanks to each of you for your ideas, comments and recommendations. I would
agree that a pivot table might be a better fit in that instance. I am trying
to understand the "D" functions, however. I have succeeded in getting a
successful response with single criterion and even with "AND" criteria. That
is when the AND is two separate fields. What I am still having trouble
entering is a "RANGE" of criteria, such as all entries between, say 10/1/06
and 10/31/06. Of course in Access I simply enter = 10/1/06 and <=10/31/06 in
a criteria, but I have not found how to do this in the Excel criterion cells.
Any suggestions? Again I am trying to understand these D functions and their
use.

Thanks

"Debra Dalgleish" wrote:

Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks to you both, made all the difference in the world, adding a second
"date' column in my criteria. I get tunnel vision sometimes and need someone
to strike a match so I see my way out. Great service you performn here!

"Peo Sjoblom" wrote:

Use this criteria

Date Date Name Variance
="=3/01/06" ="<=3/31/6"


then I used this formula

=DAVERAGE(Variances, "Variance", A1:B2)

if you want to use 2 input cells (start date in E1 and end date in F1)
and put the dates there you can use

Date Date Name Variance
="="&E1 ="="&E1

it will return the date serial number in the cells which looks a bit
strange

=38777 and <=38807

anyway, that will work fine


Regards,

Peo Sjoblom




JR Hester wrote:
Thanks to each of you for your ideas, comments and recommendations. I would
agree that a pivot table might be a better fit in that instance. I am trying
to understand the "D" functions, however. I have succeeded in getting a
successful response with single criterion and even with "AND" criteria. That
is when the AND is two separate fields. What I am still having trouble
entering is a "RANGE" of criteria, such as all entries between, say 10/1/06
and 10/31/06. Of course in Access I simply enter = 10/1/06 and <=10/31/06 in
a criteria, but I have not found how to do this in the Excel criterion cells.
Any suggestions? Again I am trying to understand these D functions and their
use.

Thanks

"Debra Dalgleish" wrote:

Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

My pleasure,

once you get the hang of it it is very useful, plus you also learn how
to do the criteria for the advanced filter which is very powerful and
very fast


Peo Sjoblom

JR Hester wrote:
Thanks to you both, made all the difference in the world, adding a second
"date' column in my criteria. I get tunnel vision sometimes and need someone
to strike a match so I see my way out. Great service you performn here!

"Peo Sjoblom" wrote:

Use this criteria

Date Date Name Variance
="=3/01/06" ="<=3/31/6"


then I used this formula

=DAVERAGE(Variances, "Variance", A1:B2)

if you want to use 2 input cells (start date in E1 and end date in F1)
and put the dates there you can use

Date Date Name Variance
="="&E1 ="="&E1

it will return the date serial number in the cells which looks a bit
strange

=38777 and <=38807

anyway, that will work fine


Regards,

Peo Sjoblom




JR Hester wrote:
Thanks to each of you for your ideas, comments and recommendations. I would
agree that a pivot table might be a better fit in that instance. I am trying
to understand the "D" functions, however. I have succeeded in getting a
successful response with single criterion and even with "AND" criteria. That
is when the AND is two separate fields. What I am still having trouble
entering is a "RANGE" of criteria, such as all entries between, say 10/1/06
and 10/31/06. Of course in Access I simply enter = 10/1/06 and <=10/31/06 in
a criteria, but I have not found how to do this in the Excel criterion cells.
Any suggestions? Again I am trying to understand these D functions and their
use.

Thanks

"Debra Dalgleish" wrote:

Another option is to use a pivot table to summarize the data. You could
view the results by name or group the dates to show a specific year or
month. There are pivot table instructions and links he

http://www.contextures.com/xlPivot01.html


JR Hester wrote:
I am attempting to use Dsum and Daverage with a date range. Below is sample
of the data I have defined ranges named CRITERION as A1:c3, Variances as
A6:C13. My formula is =Daverage(Variances, 3, Criterion). This works for
individuals in name column, but not for the date values I have entered here.
Trying to get all March entries,
#1. can you show me the error of my way?
#2 Is it possible to use actual cell references instead of named ranges to
define the criterion data?

A B C
1 Date Name Variance
2 =3/01/06
3 <=3/31/06
4
5
6 Date Name Variance
7 3/15/06 Fred $(12.23) Subtotal $(26.82)
8 4/12/06 Julie $15.85 Average $(3.83)
9 3/18/06 Poncho $3.35 Count 7
10 5/10/06 Fred $(31.90)
11 3/30/06 Julie $(19.85)
12 3/10/06 Allie $12.81
13 4/12/06 Fred $5.15

Thanks for your suggestions.

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Peo Sjoblom wrote...
....
once you get the hang of it it is very useful, plus you also learn how
to do the criteria for the advanced filter which is very powerful and
very fast
....

While advanced filters are useful from time to time, D-functions as
implemented in Excel usually become much more of a headache than a help
when different calls use different criteria. Definitely one of Excel's
most archaic features.

Definitely one of Excel's most archaic features

Which is why I prefer the alternatives.

Biff

"Harlan Grove" wrote in message
oups.com...
Peo Sjoblom wrote...
...
once you get the hang of it it is very useful, plus you also learn how
to do the criteria for the advanced filter which is very powerful and
very fast
...

While advanced filters are useful from time to time, D-functions as
implemented in Excel usually become much more of a headache than a help
when different calls use different criteria. Definitely one of Excel's
most archaic features.