DMAX DMIN Question

[RobbertH]

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

 

[sanrv1f]

Based on your description, it looks like array entered MIN / MAX formulas would do the job for you, something like,

Excel Workbook
	M	N	O
1	Main Category	Sub Category	Value
2	Main 1	Sub 1	45
3	Main 2	Sub 2	43
4	Main 1	Sub 3	37
5	Main 1	Sub 2	45
6	Main 1	Sub 3	25
7	Main 2	Sub 3	73
8	Main 2	Sub 2	85
9	Main 2	Sub 1	58
10	Main 2	Sub 5	26
11	Main 1	Sub 2	22
12	Main 1	Sub 1	32
13	Main 1	Sub 5	43
14	Main 1	Sub 4	83
Sheet6

Excel 2003

Excel Workbook
	R	S	T
1	Main Category	Sub Category	Min/Max
2	Main 1	Sub 1	32
3	Main 1	Sub 3	37
Sheet6

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Aladin Akyurek]

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