CSE formula disregard blank cells
 

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

CSE formula disregard blank cells
 

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

Tyro

"pdberger" wrote in message
...
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

CSE formula disregard blank cells
 

Sorry, not yet awake.

Tyro

"pdberger" wrote in message
...
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

CSE formula disregard blank cells
 

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

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

Rick


"pdberger" wrote in message ...
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

CSE formula disregard blank cells
 

=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" wrote in message
...
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

CSE formula disregard blank cells
 

Bob & Rick --

Thanks to you both. Hours of work saved.

Peter

"Bob Phillips" wrote:

=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" wrote in message
...
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