Lowest numbers

[Guest]

I'll try to explain my situation...

I have a budget in Excel that goes until the end of 2006.

In one colum ("G"), I have the remaining balance of my bank account. In the
next colum ("H"), I put an X in the cells where the dates have allready
passed.

I would like to have, in cell "F2", the lowest amount of money I have left
until the end of my budget.

If I'm not clear enough, please send my a private message and I'll send you
an example of what I'm looking for...

Thanks!!!

 

[Biff]

Hi!

If I'm not clear enough

Well, I'll take a guess anyhow!

Entered with the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(H:H<>"X",G:G))

Biff

 

[Guest]

And where do I put this???

Hi!


Well, I'll take a guess anyhow!

Entered with the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(H:H<>"X",G:G))

Biff

 

[Peo Sjoblom]

F2?

--
Regards,

Peo Sjoblom

And where do I put this???

 

[Jim May]

Biff:
Using H:H and G:G produced #num!
Editing to H3:H50 and G3:G50 produced $7,500.00 << Correct Amt
Both entered as CSE.
Are complete column references not usable for some reason?
TIA,
Jim

 

[Ron Rosenfeld]

Are complete column references not usable for some reason?

You cannot use complete column references in array-formulas. The
<ctrl><shift><enter> sequence makes this an array formula.

Worksheet arrays Limited by available memory. Also, arrays cannot refer to
entire columns. For example, an array cannot refer to the entire column C:C or
to the range C1:C65536. However, an array can refer to the range C165535
because the range is one row short of the maximum worksheet size and does not
include the entire C or D column.

However, you can refer to entire rows.


--ron

 

[Jim May]

Much appreciated Ron,
Jim

You cannot use complete column references in array-formulas. The
<ctrl><shift><enter> sequence makes this an array formula.

Worksheet arrays Limited by available memory. Also, arrays cannot refer to
entire columns. For example, an array cannot refer to the entire column C:C or
to the range C1:C65536. However, an array can refer to the range C165535
because the range is one row short of the maximum worksheet size and does not
include the entire C or D column.

However, you can refer to entire rows.


--ron

 

[Biff]

Hi!

Yeah, I should have caught that myself. Sometimes I hurry my response
without thinking!

In addition to Ron's info, see this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;166342

Biff