Header Ads

DaveRage Formula









This is the Database range.

Product
Wattage
Life Hours
Brand
Unit Cost
Box Quantity
Boxes In Stock
Value Of Stock

Bulb
200
3000
Horizon
£4.50
4
3
£54.00

Neon
100
2000
Horizon
£2.00
15
2
£60.00

Spot
60





£0.00

Other
10
8000
Sunbeam
£0.80
25
6
£120.00

Bulb
80
1000
Horizon
£0.20
40
3
£24.00

Spot
100
unknown
Horizon
£1.25
10
4
£50.00

Spot
200
3000
Horizon
£2.50
15
0
£0.00

Other
25
unknown
Sunbeam
£0.50
10
3
£15.00

Bulb
200
3000
Sunbeam
£5.00
3
2
£30.00

Neon
100
2000
Sunbeam
£1.80
20
5
£180.00

Bulb
100
unknown
Sunbeam
£0.25
10
5
£12.50

Bulb
10
800
Horizon
£0.20
25
2
£10.00

Bulb
60
1000
Sunbeam
£0.15
25
0
£0.00

Bulb
80
1000
Sunbeam
£0.20
30
2
£12.00

Bulb
100
2000
Horizon
£0.80
10
5
£40.00

Bulb
40
1000
Horizon
£0.10
20
5
£10.00










To calculate the Average cost of a particular Brand of bulb.















Brand
These two cells are the Criteria range.



Type the brand name :
sunbeam
















The Average cost of sunbeam is :
£1.24
 =DAVERAGE(B3:I19,F3,E23:E24)










What Does It Do ?







This function examines a list of information and produces and average.











Syntax








=DAVERAGE(DatabaseRange,FieldName,CriteriaRange)



The DatabaseRange is the entire list of information you need to examine, including the

field names at the top of the columns.





The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost" or F3.
The CriteriaRange is made up of two types of information.



   The first set of information is the name, or names, of the Fields(s) to be used as the basis
   for selecting the records, such as the category Brand or Wattage.


   The second set of information is the actual record, or records, which are to be selected, such
   as Horizon as a brand name, or 100 as the wattage.













Formatting







No special formatting is needed.















Examples
















The average Unit Cost of a particular Product of a particular Brand.














Product
Brand







Bulb
Horizon















The average of Horizon Bulb is :
£1.16
 =DAVERAGE(B3:I19,F3,E49:F50)










This is the same calculation but using the actual name "Unit Cost" instead of the cell address.












£1.16
 =DAVERAGE(B3:I19,"Unit Cost",E49:F50)










The average Unit Cost of a Bulb equal to a particular Wattage.














Product
Wattage







Bulb
100















Average of Bulb 100 is :
£0.53
 =DAVERAGE(B3:I19,"Unit Cost",E60:F61)










The average Unit Cost of a Bulb less then a particular Wattage.














Product
Wattage







Bulb
<100















Average of Bulb <100 is :
£0.17
 =DAVERAGE(B3:I19,"Unit Cost",E67:F68)










Powered by Blogger.