Array formula
 

Hi,

i have a range say A1:A100 containing numbers and i have a number in a cell
say B1, what i want to do is to have smillest number in the range A1:A100
that is bigger than the number that i have in B1, i tried the formula below
as an array formula but it does not work

=MIN(A1:A100B1)

any help would be appreciatd.

Thanks,
--
Farhad Hodjat

Array formula
 

The formula A1:A100B1 returns either TRUE or FALSE. Thus, when placed in
the MIN function, only 0 or 1 would be returned. Try including an IF
statement to filter out numbers less than B1.

=MIN(IF(A1:A10B1,A1:A10,""))

Entered as an array formula.

HTH,
Elkar


"Farhad" wrote:

Hi,

i have a range say A1:A100 containing numbers and i have a number in a cell
say B1, what i want to do is to have smillest number in the range A1:A100
that is bigger than the number that i have in B1, i tried the formula below
as an array formula but it does not work

=MIN(A1:A100B1)

any help would be appreciatd.

Thanks,
--
Farhad Hodjat

Array formula
 

One way (array-entered):

=MIN(IF(A1:A100B1, A1:A100))


In article ,
Farhad wrote:

Hi,

i have a range say A1:A100 containing numbers and i have a number in a cell
say B1, what i want to do is to have smillest number in the range A1:A100
that is bigger than the number that i have in B1, i tried the formula below
as an array formula but it does not work

=MIN(A1:A100B1)

any help would be appreciatd.

Thanks,

Array formula
 

=MIN(IF(A1:A100B1,A1:A100))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Farhad" wrote in message
...
Hi,

i have a range say A1:A100 containing numbers and i have a number in a
cell
say B1, what i want to do is to have smillest number in the range A1:A100
that is bigger than the number that i have in B1, i tried the formula
below
as an array formula but it does not work

=MIN(A1:A100B1)

any help would be appreciatd.

Thanks,
--
Farhad Hodjat