finding maximum, minimum in a range consists both Positive and Negative numbers

[Praveen]

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.

 

[Roger Govier]

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$1>0,$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.

 

[Praveen]

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$1>0,$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








- Show quoted text -

Thank you,
it is working.