| View previous topic :: View next topic |
| Author |
Message |
pcman312
Newbie
Joined: 08 May 2012
Posts: 3
|
 Posted: Tue May 08, 2012 4:04 pm Post subject: Calc dmin/dmax questions |
 |
|
I'm trying to do a minimum/maximum (and some other functions) using a conditional. I'm trying to use dmin/dmax for this, but am becoming frustrated since the only non-error I can get is 0.
I have the following (fake) data:
| Code: | Col 1 Col 2
A 3
A 2
A 5
B 3
B 1 |
I want to get the min/max value in Col 2 where Col 1 = A, B and so on.
I tried using this example: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_DMIN_function
I even copied it verbatim into Calc, but the result it gave was 0, not 300 as described in the example. |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 08, 2012 4:45 pm Post subject: |
|
|
Your numbers are text. Chars that happen to be digits.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
pcman312
Newbie
Joined: 08 May 2012
Posts: 3
|
| Posted: Wed May 09, 2012 8:00 am Post subject: |
|
|
I tried converting them to numbers:
Attempt 1: Format->Cells... -> Number/General
Attempt 2: Put an = in front of all numbers
Attempt 3: Wrapped each number in VALUE()
All of which still returned 0. |
|
| Back to top |
|
|
pcman312
Newbie
Joined: 08 May 2012
Posts: 3
|
| Posted: Wed May 09, 2012 8:04 am Post subject: |
|
|
| If I change the search criteria from 2 to >2, it works and gives me 150. I'm clearly not doing something needed to do an equals rather than a greater/less than. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed May 09, 2012 8:59 am Post subject: |
|
|
Works for me.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8982
Location: Lexinton, Kentucky, USA
|
| Posted: Wed May 09, 2012 11:05 am Post subject: |
|
|
Set your spreadsheet up as follows.
Cells A1:B6
| Code: | Col 1 Col 2
A 3
A 2
A 5
B 3
B 1
|
Note - the numbers in column B will be right aligned, as opposed left aligned as show
above, if they are in fact numbers and not text.*
Cells A8:B9
Your formula in any cell but those above is
=DMIN(A1:B6;2;A8:B9) or
=DMIN(A1:B6;B1;A8:B9) or
=DMIN(A1:B6;"Col 2";A8:B9)
and should return 2.
* Converting text to numbers - usually works.
Find and Replace
Search = ^.
Replace = &
Under More Option check Regular Expressions and then do a Replace All. |
|
| Back to top |
|
|
|
