If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding Minimum value
Hello,
I know the formula for finding the minimum value in a range is =MIN(G7:G31) But how do you find the minimum value and ignore all the 0 values? Thanks Ruan |
#2
|
|||
|
|||
Finding Minimum value
Assuming all data is positive and you just want to exclude 0s, then
=MIN(IF(A1:A1000,A1:A100)) array entered using CTRL+SHIFT+ENTER -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Ruan" wrote in message ... Hello, I know the formula for finding the minimum value in a range is =MIN(G7:G31) But how do you find the minimum value and ignore all the 0 values? Thanks Ruan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.668 / Virus Database: 430 - Release Date: 24/04/2004 |
#3
|
|||
|
|||
Finding Minimum value
Ken is right. the only difference I would use if you do want to unclude negative values is to change the argument to =MIN(IF(or(A1:A1000,a1:a1000),A1:A100)) This will let you select the negatives as well
|
#4
|
|||
|
|||
Finding Minimum value
With negatives in there but excl 0s
=MIN(IF(A1:A1000,A1:A100)) array entered -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Bruce N." wrote in message ... Ken is right. the only difference I would use if you do want to unclude negative values is to change the argument to =MIN(IF(or(A1:A1000,a1:a1000),A1:A100)) This will let you select the negatives as well --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.668 / Virus Database: 430 - Release Date: 24/04/2004 |
#5
|
|||
|
|||
Finding Minimum value
Thanks Bruce and Ken. Ken's formula is what I needed, as I am looking for
the minimum percentage above 0 in a range. Ruan "Ken Wright" wrote in message ... With negatives in there but excl 0s =MIN(IF(A1:A1000,A1:A100)) array entered -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Bruce N." wrote in message ... Ken is right. the only difference I would use if you do want to unclude negative values is to change the argument to =MIN(IF(or(A1:A1000,a1:a1000),A1:A100)) This will let you select the negatives as well --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.668 / Virus Database: 430 - Release Date: 24/04/2004 |
Thread Tools | |
Display Modes | |
|
|