DMIN Function (Functions in Excel)
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 | 1 | £37.50 |
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 | 1 | £3.75 |
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 lowest Value Of Stock of a particular Brand of bulb.
Brand | These two cells are the Criteria range. | |
Type the brand name : | Horizon | |
The MIN value of Horizon is : | £10.00 | =DMIN(B3:I19,I3,E23:E24) |
What Does It Do ? |
This function examines a list of information and produces smallest value from a specified column. |
Syntax |
=DMIN(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 pick the Min from, such as "Value Of Stock" or I3. |
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 lowest Value Of Stock of a particular Product of a particular Brand. |
Product | Brand | |
Bulb | sunbeam | |
The lowest value is : | £3.75 | =DMIN(B3:I19,I3,E49:F50) |
This is the same calculation but using the name "Value Of Stock" instead of the cell address.
£3.75 | =DMIN(B3:I19,"Value Of Stock",E49:F50) |
The lowest Value Of Stock of a Bulb equal to a particular Wattage. |
Product | Wattage | |
Bulb | 100 | |
The lowest Value Of Stock is : | £12.50 | =DMIN(B3:I19,"Value Of Stock",E60:F61) |
The lowest Value Of Stock of a Bulb between two Wattage values. |
Product | Wattage | Wattage | |
Bulb | >=80 | <=100 | |
The lowest Value Of Stock is : | £12.00 | =DMIN(B3:I19,"Value Of Stock",E67:G68) | |
0 comments:
Post a Comment