MAX, MIN without "0"

[Flats]

Hi,

I'm trying to produce the maximum and minimum values from a range but
don't want blanks to be returned as 0.

The basic formula is =MIN(B4:BU4)

I tried =IF(LEN(MIN(B4:BU4))=0,"",MIN(B4:BU4)) but it still return
0's

Thank

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MIN(IF(B4:BU4<>0,B4:BU4))

 

[Frank Kabel]

Hi
use the following array formula (entered with CTRL+SHIFT+ENTER)
=MIN(IF(B4:BU4<>0,B4,BU4))
or
=MAX(IF(B4:BU4<>0,B4,BU4))

 

[Flats]

Hi,

Thanks for the reply, what I meant was I need a formula that doesn'
return a 0 because of blank fields.

i.e. a dataset like below
23,10,"",6 will return a MIN value of 0 instead of 6

Thank

 

[Frank Kabel]

Hi
the formulas should work. MIN will ignore these cells (at least it does
in my tests). So give it a try

 

[Flats]

Realised my mistake

Thank

 

[Hans]

Using =IF(ISBLANK(),"", MIN(B4:BU4) might help.

 

[Jozef]

Hi,

I'm trying to produce the maximum and minimum values from a range but I
don't want blanks to be returned as 0.

The basic formula is =MIN(B4:BU4)

I tried =IF(LEN(MIN(B4:BU4))=0,"",MIN(B4:BU4)) but it still returns
0's

Thanks

Hi,

The minimum value of an empty cell is 0, and the length of digit 0 is
1, so it works correct. Try this:
=IF(SUM(B4:BU4)=0,"",MIN(B4:BU4))

It will work correct if numbers in cells are positive (without minus
sign).
If numbers are with signs, number -5 added to +5 will give sum=0, and
you will get empty cell instead of min=-5.

If you want to check empty cells in range(B4:BU4) use this:
=IF(COUNTIF(B4:BU4,"")=72,"",MIN(B4:BU4))

bye,

Jozef

 

[AlfD]

Hi!

Strange: MAX and MIN work ok for me whether the data includes 0,blank
negative numbers.

Are the cells actually empty or are there formulae in them?
Have you got the Tools|Options setting to show zeroes as blanks?

Al