Hi , i need help to write a user defined functions to find positive
maximum and positive minimum
and also negative maximum and negative minimum from a range consists
of both positive and
negative numbers.

ex: 15,18,12,7,3,-9,-13,-23
In my requirement the result should come as
Positive Max: 18
Positive Minimum :3
Negative Maximum: -9
Negative minimum : -23

Thanks in advance for your response.

Sure, I can help you with that! Here's a step-by-step guide to creating a user-defined function in Excel to find the positive maximum, positive minimum, negative maximum, and negative minimum from a range of numbers that includes both positive and negative values:

1. Open a new or existing Excel workbook and press ALT + F11 to open the Visual Basic Editor.
2. In the Visual Basic Editor, click on "Insert" from the top menu and select "Module" to create a new module.
3. In the new module, type the following code:
   
   
   Formula:

   Function PosMaxMinNegMaxNegMin(rng As Range) As Variant
Dim posMax As Double, posMin As Double, negMax As Double, negMin As Double
posMax = WorksheetFunction.Max(0, rng)
posMin = WorksheetFunction.Min(0, rng)
negMax = WorksheetFunction.Max(rng, 0)
negMin = WorksheetFunction.Min(rng, 0)
PosMaxMinNegMaxNegMin = Array(posMax, posMin, negMax, negMin)
End Function 

4. Save the module and return to your Excel worksheet.
5. In a cell where you want to display the results, enter the following formula: =PosMaxMinNegMaxNegMin(A1:A8) (assuming your range of numbers is in cells A1 through A8).
6. Press Enter and the results will appear in the cell as an array with four values: positive maximum, positive minimum, negative maximum, and negative minimum.

That's it! This user-defined function will find the positive maximum, positive minimum, negative maximum, and negative minimum from a range of numbers that includes both positive and negative values. Let me know if you have any questions or need further assistance.

Hi
The Max and Min are straightforward
=MAX($A$1:$H$1) 18
=MIN($A$1:$H$1) -23

for the Min positive, the array formula
{=MIN(IF($A$1:$H$10,$A$1:$H$1))} 3
for the Max negative
{=MAX(IF($A$1:$H$1<0,$A$1:$H$1))} -9

For array formulae, commit or Edit using Control+Shift+Enter (CSE) not
just Enter.
Do not type the curly braces { } yourself, if you use CSE, Excel will
insert them for you.
--
Regards

Roger Govier


"Praveen" wrote in message
ups.com...
Hi , i need help to write a user defined functions to find positive
maximum and positive minimum
and also negative maximum and negative minimum from a range consists
of both positive and
negative numbers.

ex: 15,18,12,7,3,-9,-13,-23
In my requirement the result should come as
Positive Max: 18
Positive Minimum :3
Negative Maximum: -9
Negative minimum : -23

Thanks in advance for your response.

On May 14, 12:50 pm, "Roger Govier"
wrote:
Hi
The Max and Min are straightforward
=MAX($A$1:$H$1) 18
=MIN($A$1:$H$1) -23

for the Min positive, the array formula
{=MIN(IF($A$1:$H$10,$A$1:$H$1))} 3
for the Max negative
{=MAX(IF($A$1:$H$1<0,$A$1:$H$1))} -9

For array formulae, commit or Edit using Control+Shift+Enter (CSE) not
just Enter.
Do not type the curly braces { } yourself, if you use CSE, Excel will
insert them for you.
--
Regards

Roger Govier

"Praveen" wrote in message

ups.com...



Hi , i need help to write a user defined functions to find positive
maximum and positive minimum
and also negative maximum and negative minimum from a range consists
of both positive and
negative numbers.

ex: 15,18,12,7,3,-9,-13,-23
In my requirement the result should come as
Positive Max: 18
Positive Minimum :3
Negative Maximum: -9
Negative minimum : -23

Thanks in advance for your response.- Hide quoted text -

- Show quoted text -

Thank you,
it is working.