View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default 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