How to get lowest value excluding blanks
 

I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a column.
What formula would I need to use?
Thanks in advance!

Hi John,

The Min function ignores blank cells, so:

=Min(A1:A100)

--

---
Regards,
Norman



"JohnT" wrote in message
...
I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a column.
What formula would I need to use?
Thanks in advance!

in addtion to the elegant solution by Mr. Jones if you want to get the
minimum of those values more than a number for example more than 5 use this
formula givn by
Debra Dalgleish
your data is in C2 to C14

=MIN(IF(C2:C120,C2:C12))

and then (this is importatn)
controll+shift +enter.


Norman Jones wrote in message
...
Hi John,

The Min function ignores blank cells, so:

=Min(A1:A100)

--

---
Regards,
Norman



"JohnT" wrote in message
...
I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a
column.
What formula would I need to use?
Thanks in advance!

That works fantastically!
If you have the time, please explain the significance of doing
Ctrl+Shft+Enter.
Thanks,
JT

"R.VENKATARAMAN" wrote:

in addtion to the elegant solution by Mr. Jones if you want to get the
minimum of those values more than a number for example more than 5 use this
formula givn by
Debra Dalgleish
your data is in C2 to C14

=MIN(IF(C2:C120,C2:C12))

and then (this is importatn)
controll+shift +enter.


Norman Jones wrote in message
...
Hi John,

The Min function ignores blank cells, so:

=Min(A1:A100)

--

---
Regards,
Norman



"JohnT" wrote in message
...
I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a
column.
What formula would I need to use?
Thanks in advance!

Hi

there are a number of formulas called "array formulas" and in some cases,
"normal" formulas whoes functionality is extended when entered as an array
formula.

To enter an array formula you need to use ctrl & shift & enter instead of
just enter. When you look in the formula bar after entering an array
formula you will see { } around the formula - these indicate that it has
been array-entered.

An array formula basically works on a number of cells rather than just one
cell.
In the formula you were given
=MIN(IF(C2:C120,C2:C12))

you need to test if more than one cell (e.g. C2:C12 is greater than 0),
normally you can only ask this question of one cell (ie IF(C20 ... )
however, if you array enter it the question is asked individually on each
cell within the range.

Hope this explaination doesn't make you more confused then when you started
:)

Cheers
JulieD



"JohnT" wrote in message
...
That works fantastically!
If you have the time, please explain the significance of doing
Ctrl+Shft+Enter.
Thanks,
JT

"R.VENKATARAMAN" wrote:

in addtion to the elegant solution by Mr. Jones if you want to get the
minimum of those values more than a number for example more than 5 use
this
formula givn by
Debra Dalgleish
your data is in C2 to C14

=MIN(IF(C2:C120,C2:C12))

and then (this is importatn)
controll+shift +enter.


Norman Jones wrote in message
...
Hi John,

The Min function ignores blank cells, so:

=Min(A1:A100)

--

---
Regards,
Norman



"JohnT" wrote in message
...
I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a
column.
What formula would I need to use?
Thanks in advance!

Do note though that array formulas will use more resource than the MIN formula
that was quoted. For odd formulas this will usually be totally irrelevant, but
if you had hundreds of them across a range then you may well start to see an
impact in terms of calculation time.

Also, the example you were given only gave you the smallest value greater than 0
(as opposed to greater than 5 as per the text) which did no more than MIN. To
get the smallest value above 5 you would have needed to change that 0 to a 5:-

=MIN(IF(C2:C125,C2:C12))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"JohnT" wrote in message
...
That works fantastically!
If you have the time, please explain the significance of doing
Ctrl+Shft+Enter.
Thanks,
JT

"R.VENKATARAMAN" wrote:

in addtion to the elegant solution by Mr. Jones if you want to get the
minimum of those values more than a number for example more than 5 use this
formula givn by
Debra Dalgleish
your data is in C2 to C14

=MIN(IF(C2:C120,C2:C12))

and then (this is importatn)
controll+shift +enter.


Norman Jones wrote in message
...
Hi John,

The Min function ignores blank cells, so:

=Min(A1:A100)

--

---
Regards,
Norman



"JohnT" wrote in message
...
I want to display the lowest value e.g. 22.68, 27.89, 28.01... in a
column.
What formula would I need to use?
Thanks in advance!







---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.804 / Virus Database: 546 - Release Date: 30/11/2004