J
JohnT
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!
What formula would I need to use?
Thanks in advance!
N
Norman Jones
Hi John,
The Min function ignores blank cells, so:
=Min(A1:A100)
The Min function ignores blank cells, so:
=Min(A1:A100)
J
JohnT
That works fantastically!
If you have the time, please explain the significance of doing
Ctrl+Shft+Enter.
Thanks,
JT
If you have the time, please explain the significance of doing
Ctrl+Shft+Enter.
Thanks,
JT
J
JulieD
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:C12>0,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(C2>0 ... )
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
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:C12>0,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(C2>0 ... )
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
K
Ken Wright
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:C12>5,C2:C12))
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:C12>5,C2:C12))