Separate MAX for different formats
 

Some numbers in a column have a General format, and some have a $ format.
Can I evaluate a MAX function separately for each format?
--
alexander

Separate MAX for different formats
 

Are you up for a UDF (User Defined Function) solution? Press Alt+F11 to go
into the VB editor, click Insert/Module from its menu bar and copy/paste the
following UDFs into the code window that opened up...

Function MaxCurrency(Rng As Range) As Double
Dim R As Range
MaxCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) = "$" Then
If MaxCurrency < R.Value Then MaxCurrency = R.Value
End If
Next
End Function

Function MaxNonCurrency(Rng As Range) As Double
Dim R As Range
MaxNonCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) < "$" Then
If MaxNonCurrency < R.Value Then MaxNonCurrency = R.Value
End If
Next
End Function

To use these function, just go back to your worksheet and enter these
formulas into whatever cells you want the values in...

=MaxCurrency(A1:A10)

=MaxNonCurrency(A1:A10)

where you would replace my example range A1:A10 range with your actual
range.

--
Rick (MVP - Excel)


"alexander" wrote in message
...
Some numbers in a column have a General format, and some have a $ format.
Can I evaluate a MAX function separately for each format?
--
alexander

Separate MAX for different formats
 

Yes, with a helper column. Say column A has both general numbers and dollar
values.

In B1 enter:

=LEFT(CELL("format",A1),1) and copy down

This exposes the format of the cells in column A. For example:

1 G
2 G
3 G
$1.00 C
$3.00 C
$4.00 C
123 G
-10 G
2 G
7 G

In another cell we enter:

=MAX(IF(B1:B10="C",A1:A10,"")) will display 4

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key
--
Gary''s Student - gsnu200831


"alexander" wrote:

Some numbers in a column have a General format, and some have a $ format.
Can I evaluate a MAX function separately for each format?
--
alexander

Separate MAX for different formats
 

Rick, the UDFs work great. How do I make MAXCurrency refresh? After I insert
or append currency values to the original list, MAXNonCurrency refreshes as
though the new numbers have a General format, and MAXCurrency doesn't change.

--
alexander


"Rick Rothstein" wrote:

Are you up for a UDF (User Defined Function) solution? Press Alt+F11 to go
into the VB editor, click Insert/Module from its menu bar and copy/paste the
following UDFs into the code window that opened up...

Function MaxCurrency(Rng As Range) As Double
Dim R As Range
MaxCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) = "$" Then
If MaxCurrency < R.Value Then MaxCurrency = R.Value
End If
Next
End Function

Function MaxNonCurrency(Rng As Range) As Double
Dim R As Range
MaxNonCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) < "$" Then
If MaxNonCurrency < R.Value Then MaxNonCurrency = R.Value
End If
Next
End Function

To use these function, just go back to your worksheet and enter these
formulas into whatever cells you want the values in...

=MaxCurrency(A1:A10)

=MaxNonCurrency(A1:A10)

where you would replace my example range A1:A10 range with your actual
range.

--
Rick (MVP - Excel)


"alexander" wrote in message
...
Some numbers in a column have a General format, and some have a $ format.
Can I evaluate a MAX function separately for each format?
--
alexander

Separate MAX for different formats
 

Hmm! That's interesting. I'm not entirely sure why one is updating and the
other isn't. The fix is easy enough though. Use this MaxNonCurrency function
instead...

Function MaxNonCurrency(Rng As Range) As Double
Dim R As Range
Application.Volatile
MaxNonCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) < "$" Then
If MaxNonCurrency < R.Value Then MaxNonCurrency = R.Value
End If
Next
End Function

Note: The only difference between this function and the one I originally
posted is the Application.Volatile statement.

--
Rick (MVP - Excel)


"alexander" wrote in message
...
Rick, the UDFs work great. How do I make MAXCurrency refresh? After I
insert
or append currency values to the original list, MAXNonCurrency refreshes
as
though the new numbers have a General format, and MAXCurrency doesn't
change.

--
alexander


"Rick Rothstein" wrote:

Are you up for a UDF (User Defined Function) solution? Press Alt+F11 to
go
into the VB editor, click Insert/Module from its menu bar and copy/paste
the
following UDFs into the code window that opened up...

Function MaxCurrency(Rng As Range) As Double
Dim R As Range
MaxCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) = "$" Then
If MaxCurrency < R.Value Then MaxCurrency = R.Value
End If
Next
End Function

Function MaxNonCurrency(Rng As Range) As Double
Dim R As Range
MaxNonCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) < "$" Then
If MaxNonCurrency < R.Value Then MaxNonCurrency = R.Value
End If
Next
End Function

To use these function, just go back to your worksheet and enter these
formulas into whatever cells you want the values in...

=MaxCurrency(A1:A10)

=MaxNonCurrency(A1:A10)

where you would replace my example range A1:A10 range with your actual
range.

--
Rick (MVP - Excel)


"alexander" wrote in message
...
Some numbers in a column have a General format, and some have a $
format.
Can I evaluate a MAX function separately for each format?
--
alexander

Separate MAX for different formats
 

The values in my column are the product of three values, one of which is
entered as currency. The PRODUCT function returns a value in plain Number
format. Changing the format to Currency doesn't refresh MAXCurrency.
--
alexander


"Rick Rothstein" wrote:

Are you up for a UDF (User Defined Function) solution? Press Alt+F11 to go
into the VB editor, click Insert/Module from its menu bar and copy/paste the
following UDFs into the code window that opened up...

Function MaxCurrency(Rng As Range) As Double
Dim R As Range
MaxCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) = "$" Then
If MaxCurrency < R.Value Then MaxCurrency = R.Value
End If
Next
End Function

Function MaxNonCurrency(Rng As Range) As Double
Dim R As Range
MaxNonCurrency = 1E-308
For Each R In Rng
If Left(R.Text, 1) < "$" Then
If MaxNonCurrency < R.Value Then MaxNonCurrency = R.Value
End If
Next
End Function

To use these function, just go back to your worksheet and enter these
formulas into whatever cells you want the values in...

=MaxCurrency(A1:A10)

=MaxNonCurrency(A1:A10)

where you would replace my example range A1:A10 range with your actual
range.

--
Rick (MVP - Excel)


"alexander" wrote in message
...
Some numbers in a column have a General format, and some have a $ format.
Can I evaluate a MAX function separately for each format?
--
alexander