Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separate two dates from a text string into two separate cells | Excel Discussion (Misc queries) | |||
How do I keep graph "data table" formats in separate excel workboo | Charts and Charting in Excel | |||
How do I keep cell color formats separate from text during A-Z sor | Excel Worksheet Functions | |||
Save 2 separate data imports in separate worksheets on the same ex | Excel Worksheet Functions | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) |