Guys,

i have a data set thus,
20
21
21.5
27
24
0
0
19.5

Now the range formula will take 27-0 and give me the answer as 27
but if I want the zeros to be discounted from the range formula is it
possible??
that is i want the range formula to take the max as 27 and in this case the
minimum as 19.5, can it be done??

Hi,

Try this

=MIN(IF(A1:A80,A1:A8))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

=Max(a1:a8)

for the max

Mike

"ace" wrote:

Guys,

i have a data set thus,
20
21
21.5
27
24
0
0
19.5

Now the range formula will take 27-0 and give me the answer as 27
but if I want the zeros to be discounted from the range formula is it
possible??
that is i want the range formula to take the max as 27 and in this case the
minimum as 19.5, can it be done??

Is this actually a formula question?
Try the array* function
=MAX(A2:A10)-MIN(IF(A2:A10<0,A2:A10,"x"))

*Use Ctrl+Shift+Enter to confirm formula.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ace" wrote:

Guys,

i have a data set thus,
20
21
21.5
27
24
0
0
19.5

Now the range formula will take 27-0 and give me the answer as 27
but if I want the zeros to be discounted from the range formula is it
possible??
that is i want the range formula to take the max as 27 and in this case the
minimum as 19.5, can it be done??

Hi
If data is in A1:A8 then

=MIN(IF(A1:A80,A1:A8,""))

and enter as an array formula using Ctr+shift+enter

regards
Paul

On Apr 2, 5:16*pm, ace wrote:
Guys,

i have a data set thus,
20
21
21.5
27
24
0
0
19.5

Now the range formula will take 27-0 and give me the answer as 27
but if I want the zeros to be discounted from the range formula is it
possible??
that is i want the range formula to take the max as 27 and in this case the
minimum as 19.5, can it be done??

Luke,

your formula worked like a charm.

thanks,

appreciate it,
Ace

"Mike H" wrote:

Hi,

Try this

=MIN(IF(A1:A80,A1:A8))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

=Max(a1:a8)

for the max

Mike

"ace" wrote:

Guys,

i have a data set thus,
20
21
21.5
27
24
0
0
19.5

Now the range formula will take 27-0 and give me the answer as 27
but if I want the zeros to be discounted from the range formula is it
possible??
that is i want the range formula to take the max as 27 and in this case the
minimum as 19.5, can it be done??