Calculate MIN excluding zero
 

I am trying to calculate the MIN of several non-consecutive columns, but
want the calculation to ignore columns that have no value or zero (0) in
them.

The columns being queried have calculations of their own in them, but
hopefully that isn't part of the problem.

Anyway, the calculation I am using is =MIN(G291,I291,K291,M291), which
will return a zero/null value if one of the cells is empty. I did a
search and MSFT tells me I should use =MIN(A1:A100,A1:A10), which
doesn't work for me.

Suggestions?


--
gezuvor
------------------------------------------------------------------------
gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383
View this thread: http://www.excelforum.com/showthread...hreadid=513898

Calculate MIN excluding zero
 

The search feature on this site is rubbish, but Google found this...

http://www.excelforum.com//showthread.php?t=482399

Works perfectly. Thank you bpeltzer!


--
gezuvor
------------------------------------------------------------------------
gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383
View this thread: http://www.excelforum.com/showthread...hreadid=513898

Calculate MIN excluding zero
 

Use SMALL()


=IF(SMALL(C6:G6,1)=0,SMALL(C6:G6,2),SMALL(C6:G6,1) )
--
Gary's Student


"gezuvor" wrote:


I am trying to calculate the MIN of several non-consecutive columns, but
want the calculation to ignore columns that have no value or zero (0) in
them.

The columns being queried have calculations of their own in them, but
hopefully that isn't part of the problem.

Anyway, the calculation I am using is =MIN(G291,I291,K291,M291), which
will return a zero/null value if one of the cells is empty. I did a
search and MSFT tells me I should use =MIN(A1:A100,A1:A10), which
doesn't work for me.

Suggestions?


--
gezuvor
------------------------------------------------------------------------
gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383
View this thread: http://www.excelforum.com/showthread...hreadid=513898

Calculate MIN excluding zero
 

=MIN(IF((MOD(COLUMN(G291:M291),2)=1)*(G291:M291<0 ),G291:M291))

entered with ctrl + shift & enter

and it will only check every other cell, if the formula from the link works
then this will work as well

=MIN(IF(G291:M291<0,G291:M291))

also entered with ctrl + shift & enter, if you only want values greater than
zero change
< to



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"gezuvor" wrote in
message ...

The search feature on this site is rubbish, but Google found this...

http://www.excelforum.com//showthread.php?t=482399

Works perfectly. Thank you bpeltzer!


--
gezuvor
------------------------------------------------------------------------
gezuvor's Profile:
http://www.excelforum.com/member.php...o&userid=30383
View this thread: http://www.excelforum.com/showthread...hreadid=513898

Calculate MIN excluding zero
 

More excellent replies! Thank you!!


--
gezuvor
------------------------------------------------------------------------
gezuvor's Profile: http://www.excelforum.com/member.php...o&userid=30383
View this thread: http://www.excelforum.com/showthread...hreadid=513898