Help with excel formula

F

Fran

Using database functions, I need to calculate the average orders

Worksheet A6:L184
Totals in column L
Order date is > 04/01/06
Sales Rep = Davis
Category = Beverages

My formula was
=DAVERAGE(A6:L184,L6,D6=Davis,F6=Beverages,Criteria>04/01/06)

IT DIDN'T WORK!!! CAN ANYONE HELP?
 
C

C01d

You will need to have two ranges. One for the data, the other for the
criteria.

Criteria portion:
Order Date Sales Rep Category
="=4/1/06" ="=Davis" ="=Beverages"

Data portion:
Order Date Sales Rep Category Price
< insert data here >

Assuming your criteria portion is in the range A1:C2 (including
headings), and your data portion is A4:D10 (including headings), Your
DAverage formula should look like:

=DAverage(A4:D10,"Order Price",A1:C2)

What this means is that DAverage will look into A4:D10 for your data,
compute the average on the Order Price column, filtering fields using
the criteria specified in A1:C2.

Hope this helps.
 
C

CaptainQuattro

If I read the question correctly, you need to average Sales for all
dates greater than April 1

Therefore in the criteria range, you want to omit the first column
heading and instead of ="=4/1/06" you want to enter the following
formula:
=A7>DATE(2006,4,1)
 
B

Bob Phillips

=AVERAGE(IF((D6:D184="Davis")*(F6:F184="Beverages")*(I6:I184>--"2006-04-01")
,L6:L184))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top