Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to subtract the very next LOWEST number | New Users to Excel | |||
Filling in blanks. | Excel Worksheet Functions | |||
Finding the average by dropping the lowest | Excel Worksheet Functions | |||
How can I get the lowest price, second lowest etc. from a range o. | Excel Worksheet Functions | |||
Newbie: How to round down to the lowest 5 | Excel Worksheet Functions |