DMAX DMIN Question

RobbertH

Active Member
Joined
Apr 29, 2008
Messages
310
Dear gurus,

I am struggling a bit with the DMAX and DMIN formulas i use to get the minimum and maximum values from a table.

First, how do i salve the problem of copying down the formula. Eg I have a list of different variables for with i want the minimum and maximum. I however have to specify the column name each time i have a different variable. I solved this now by adding a row in between each variable but i am sure there is a fancier solution.

Second, it seems that variables which start is identical to another variable but includes an extention (eg cola, and cola light) are taken together in picking up the minimum and maximum. I dont really understand why and have problems solving the issue.

Hope you have an idea! Thanks a lot for any help!!

Robbert
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
Based on your description, it looks like array entered MIN / MAX formulas would do the job for you, something like,
Excel Workbook
MNO
1Main CategorySub CategoryValue
2Main 1Sub 145
3Main 2Sub 243
4Main 1Sub 337
5Main 1Sub 245
6Main 1Sub 325
7Main 2Sub 373
8Main 2Sub 285
9Main 2Sub 158
10Main 2Sub 526
11Main 1Sub 222
12Main 1Sub 132
13Main 1Sub 543
14Main 1Sub 483
Sheet6
Excel 2003
Excel Workbook
RST
1Main CategorySub CategoryMin/Max
2Main 1Sub 132
3Main 1Sub 337
Sheet6
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Dear gurus,

I am struggling a bit with the DMAX and DMIN formulas i use to get the minimum and maximum values from a table.

First, how do i salve the problem of copying down the formula. Eg I have a list of different variables for with i want the minimum and maximum. I however have to specify the column name each time i have a different variable. I solved this now by adding a row in between each variable but i am sure there is a fancier solution.

Second, it seems that variables which start is identical to another variable but includes an extention (eg cola, and cola light) are taken together in picking up the minimum and maximum. I dont really understand why and have problems solving the issue.

Hope you have an idea! Thanks a lot for any help!!

Robbert

You need something like the Table method in order to invoke the same DMAX/DMIN formula for similar objects. See:

http://support.microsoft.com/default...b;en-us;282851

For "cola" and "cola light" issue, try...

F1: cola

F3:

="="&$F$1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,355
Messages
5,571,687
Members
412,412
Latest member
NWPhotoExplorer
Top