CSE formula disregard blank cells

[pdberger]

Good morning --

I'm setting up a workbook for several users, in which they will gradually
add data over the months and years ahead. I want to set it up to compare the
current month's ratios to the maxima and minima over the four years' worth
they'll eventually have. In the meantime, I need to figure out how to get
the CSE formula to disregard the cells that aren't filled in yet. Here's an
example:

A B
1 (blank) (blank)
2 1 5
3 2 4
4 3 3
5 4 2
6 5 1

I'd like a formula a la
{=min(b1:b6/a1:a6)}

Is there something I can put in the $1:$1 row to get the CSE formula to
disregard it until filled? Or is there some kind of =IF() statement I can
use inside the CSE formula?

TIA

 

[Tyro]

The MIN function ignores empty cells. Would you please show us the answer
you expect?

Tyro

 

[Tyro]

Sorry, not yet awake.

Tyro

 

[Rick Rothstein \(MVP - VB\)]

I think this CSE formula may do what you want...

=MIN(IF(A1:A6="",1E+300,B1:B6/A1:A6))

Rick

 

[Bob Phillips]

=MIN(IF(A1:A1000<>"",B1:B100/A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[pdberger]

Bob & Rick --

Thanks to you both. Hours of work saved.

Peter