Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Blade
 
Posts: n/a
Default How do I get "minimum value" in a range to NOT return zero?

I'm trying to keep the =MIN from returning 0 as the minimum value. Is this
possible?
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Blade

try
=SMALL(C1:C10,COUNTIF(C1:C10,0)+1)

where C1:C10 is the range you're looking for the minimum in.

Cheers
JulieD

"Blade" wrote in message
...
I'm trying to keep the =MIN from returning 0 as the minimum value. Is this
possible?



  #3   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

If there are only positive values

=LARGE(A1:A10,COUNTIF(A1:A10,"0"))

if there can be negative values

=MIN(IF(A1:A10<0,A1:A10))

note that the latter formula MUST be entered with ctrl + shift & enter

Regards,

Peo Sjoblom

"Blade" wrote:

I'm trying to keep the =MIN from returning 0 as the minimum value. Is this
possible?

  #4   Report Post  
Blade
 
Posts: n/a
Default

Thanks, this works great when selecting a range like A1:A:10
Is there different syntax for selecting individual cells like A1, A3, A5, A7
where A1, A3, A5, A7 represent values for a separate calculation?

"Peo Sjoblom" wrote:

If there are only positive values

=LARGE(A1:A10,COUNTIF(A1:A10,"0"))

if there can be negative values

=MIN(IF(A1:A10<0,A1:A10))

note that the latter formula MUST be entered with ctrl + shift & enter

Regards,

Peo Sjoblom

"Blade" wrote:

I'm trying to keep the =MIN from returning 0 as the minimum value. Is this
possible?

  #5   Report Post  
Myrna Larson
 
Posts: n/a
Default

Here's one way:

=MIN(IF(A1:A7<0,IF(MOD(ROW(A1:A7),2)=1,A1:A7)))

This is an array formula and must be entered with CTRL+SHIFT+ENTER.

On Tue, 1 Feb 2005 08:21:10 -0800, "Blade"
wrote:

Thanks, this works great when selecting a range like A1:A:10
Is there different syntax for selecting individual cells like A1, A3, A5, A7
where A1, A3, A5, A7 represent values for a separate calculation?

"Peo Sjoblom" wrote:

If there are only positive values

=LARGE(A1:A10,COUNTIF(A1:A10,"0"))

if there can be negative values

=MIN(IF(A1:A10<0,A1:A10))

note that the latter formula MUST be entered with ctrl + shift & enter

Regards,

Peo Sjoblom

"Blade" wrote:

I'm trying to keep the =MIN from returning 0 as the minimum value. Is

this
possible?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing a range name as an argument to the Index Function Michael Sharpe Excel Discussion (Misc queries) 3 September 5th 12 01:33 PM
Minimum value in a range > 0 Barbara Excel Discussion (Misc queries) 3 January 25th 05 05:57 PM
Function to return the latest non-zero value in a range of cells . hungryman Excel Worksheet Functions 2 January 24th 05 03:54 PM
lookup - return minimum value Steve R Excel Worksheet Functions 7 December 30th 04 05:44 AM
Excel: How to return count for each cell within date range criter. Louisa Excel Worksheet Functions 0 November 5th 04 12:58 PM


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"