The following cells on my spread sheet produce the #DIV/0! error under the
following circumstances, and I'd prefer the cells to remain "blank" until
data is input to produce the outcomes identified (which do work once data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500 range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under the
following circumstances, and I'd prefer the cells to remain "blank" until
data is input to produce the outcomes identified (which do work once data
is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

Once again Dave you saved the day. I appreciate all of your help with my
spread sheet and helping me to get rid of those division errors. You are
great. Have an awesome weekend!

Dan

"Dave Thomas" wrote:

Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under the
following circumstances, and I'd prefer the cells to remain "blank" until
data is input to produce the outcomes identified (which do work once data
is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would be
better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank" until
data is input to produce the outcomes identified (which do work once data
is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would be
better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would be
better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

On way with Excel 2007 is with "IFERROR"

=IFERROR(AVERAGE(A1:A10),"")

--
Dana DeLouis


"Dan the Man" wrote in message
...
Once again Dave you saved the day. I appreciate all of your help with my
spread sheet and helping me to get rid of those division errors. You are
great. Have an awesome weekend!

Dan

"Dave Thomas" wrote:

Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data
is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

Are you positive? Nowhere in Dan the Man posts do I see him saying that the
data will *always* be positive.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Thomas" wrote in message
et...
The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would
be better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

I have his spreadsheet

"Sandy Mann" wrote in message
...
Are you positive? Nowhere in Dan the Man posts do I see him saying that
the data will *always* be positive.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Thomas" wrote in message
et...
The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would
be better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

Always helps to have that inside track, doesn't it? <g

"Dave Thomas" wrote:

I have his spreadsheet

"Sandy Mann" wrote in message
...
Are you positive? Nowhere in Dan the Man posts do I see him saying that
the data will *always* be positive.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Thomas" wrote in message
et...
The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would
be better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error under
the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

So why did you answer here when you could have sent him an email?
His posted question doesn't say anything about positive so according to
yourself in previous postings it is better to answer with regards the OP and
assume that anything is possible. I seem to recall a certain question when
someone posted an answer assuming positive numbers and you came down on it
although in that case it was much more likely that the OP had only positive
values?


--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
I have his spreadsheet

"Sandy Mann" wrote in message
...
Are you positive? Nowhere in Dan the Man posts do I see him saying that
the data will *always* be positive.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Thomas" wrote in message
et...
The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive values?
Since the OP said "until data begins appearing in the C4:C500" it would
be better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error
under the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the C4:C500
range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

"Dave Thomas" wrote...
I have his spreadsheet
....

That just means his sample data is all positive. Doesn't mean your formula
will always work. When you gain some experience, you'll find it's actually
easier to try to foresee potential problems. Besides, how much more
complicated would it have been to suggest

=IF(COUNT(C4:C500),AVERAGE(C4:C500),"")

As for testing AO19<0, note that if AO19 contained any text or boolean
value, this expression would return TRUE. This could be a problem if AO19
contained strings of 0 or more spaces (enter a single quote only or the
formula ="" to get a string of zero spaces - it'd be text of length zero,
which is not equal to 0). If the goal is to avoid #DIV/0! when AO19 doesn't
contain a number, then just test whether it contains a number.

=IF(COUNT(AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(AO19),AL4/AO19,"")

If the goal is to test whether AO19 contains something that could be treated
as a nonzero number, use

=IF(COUNT(1/AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(1/AO19),AL4/AO19,"")

In almost all cases, the proper way to trap reasonably anticipated #DIV/0!
errors, especially from AVERAGE, is with a COUNT call.

I posted here, because he didn't e-mail me. He asked here. Now calm down.

"Peo Sjoblom" wrote in message
...
So why did you answer here when you could have sent him an email?
His posted question doesn't say anything about positive so according to
yourself in previous postings it is better to answer with regards the OP
and assume that anything is possible. I seem to recall a certain question
when someone posted an answer assuming positive numbers and you came down
on it although in that case it was much more likely that the OP had only
positive values?


--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
I have his spreadsheet

"Sandy Mann" wrote in message
...
Are you positive? Nowhere in Dan the Man posts do I see him saying that
the data will *always* be positive.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Thomas" wrote in message
et...
The data is positive

"Peo Sjoblom" wrote in message
...
What if the sum is zero in C4:C500 due to negative and positive
values?
Since the OP said "until data begins appearing in the C4:C500" it
would be better to test C4:C500 for blank cells.

--

Regards,

Peo Sjoblom

"Dave Thomas" wrote in message
. net...
Here's one way:

=IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"")
=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

"Dan the Man" wrote in message
...
The following cells on my spread sheet produce the #DIV/0! error
under the
following circumstances, and I'd prefer the cells to remain "blank"
until
data is input to produce the outcomes identified (which do work once
data is
provided):

C3:

=AVERAGE(C4:C500)

Division error will occur, until data begins appearing in the
C4:C500 range
cells.

AV4:

=SUM(AL4:AL18)/AO19

Division error will occur, until data appears in cell AO19

AQ4:

=AL4/AO19

Division error will occur, until data appears in cell AO19

Any suggestions would be greatly appreciated!

Dan

Harlan:

Your formulas:

=IF(COUNT(AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(AO19),AL4/AO19,"")
=IF(COUNT(1/AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(1/AO19),AL4/AO19,"")

Mine:

=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

AO19 is supposed to contain a numeric including 0 or blank and nothing else.
My formulas avoid the #DIV/0 error yet return an error if there is something
other than blanks or zeroes which should be fine. I would want to know if
there is garbage in that cell. If there is garbage in that cell your
formulas hide the fact by returning an empty string.

As regards the =IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"") I submitted.

Your suggestion of =IF(COUNT(C4:C500),AVERAGE(C4:C500),"") serves no good
purpose either, as it allows for non-numerics in the column.

Let us consider the C4:C500. Let us work with a smaller range, A1:A10 and
name it DATA. Let us put the values 1,2,3,4,5,-1,-2,-3,-4,-5 in DATA, and
play with the values.

I propose this formula which handles blanks, negative numbers, 0's, positive
numbers and non-blanks in DATA:

=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),IF((SU M(DATA)<0)+COUNTIF(DATA,"<0"),AVERAGE(DATA),"")," Houston,
we have a problem")

The above formula averages any combination of positive and negative numbers
including 0's, avoids #DIV/0 in the case of DATA = 0, or DATA = empty; if
the sum of DATA = 0 and DATA is composed of positive and negative numbers,
the average is computed. It also provides a warning if there are non-numeric
data in the cells. It returns the empty string in all other cases.

Now I know that you are much better versed in Excel than I. So, obviously
you can reduce this formula. I became a computer programmer at 8:15 this
morning and ventured into Excel at 8:31. Why not 8:16? Was busy sucking up
to my female boss.

I propose the concept of this formula for the purpose of discussing
invalidated data in Excel cells. I feel strongly that data should be
validated, not by Data Validation in Excel which is, as you know, so weak,
it is almost laughable. Excel data should be validated only by code. If not,
one has to resort to the formula above, as you well know.

Regards,

Dave

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
I have his spreadsheet
...

That just means his sample data is all positive. Doesn't mean your formula
will always work. When you gain some experience, you'll find it's actually
easier to try to foresee potential problems. Besides, how much more
complicated would it have been to suggest

=IF(COUNT(C4:C500),AVERAGE(C4:C500),"")

As for testing AO19<0, note that if AO19 contained any text or boolean
value, this expression would return TRUE. This could be a problem if AO19
contained strings of 0 or more spaces (enter a single quote only or the
formula ="" to get a string of zero spaces - it'd be text of length zero,
which is not equal to 0). If the goal is to avoid #DIV/0! when AO19
doesn't contain a number, then just test whether it contains a number.

=IF(COUNT(AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(AO19),AL4/AO19,"")

If the goal is to test whether AO19 contains something that could be
treated as a nonzero number, use

=IF(COUNT(1/AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(1/AO19),AL4/AO19,"")

In almost all cases, the proper way to trap reasonably anticipated #DIV/0!
errors, especially from AVERAGE, is with a COUNT call.

"Dave Thomas" wrote...
Your formulas:

=IF(COUNT(AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(AO19),AL4/AO19,"")
=IF(COUNT(1/AO19),SUM(AL4:AL18)/AO19,"")
=IF(COUNT(1/AO19),AL4/AO19,"")

Mine:

=IF(AO19<0,SUM(AL4:AL18)/AO19,"")
=IF(AO19<0,AL4/AO19,"")

AO19 is supposed to contain a numeric including 0 or blank and nothing
else. . . .

Yes, that may be what they're SUPPOSED TO contain. If this is the OP's own
workbook for the OP's own exclusive use, how likely would it be that #DIV/0!
errors before entry would be a big concern? OTOH, if this were something the
OP were making for OTHERS to use, then all bets are off whether those others
will enter only what's supposed to be entered.

My formulas avoid the #DIV/0 error yet return an error if there is
something other than blanks or zeroes which should be fine. . . .

Your formula would return numbers if AO19 evaluated to nonzero numeric text
or boolean TRUE, and would return #DIV/0! if AO19 evaluated to "0" or
boolean FALSE. That's reasonable. However, users have an annoying tendency
to use the space bar to 'clear' cells. Up to the OP to decide whether or not
this matters.

. . . I would want to know if there is garbage in that cell. If there is
garbage in that cell your formulas hide the fact by returning an empty
string.

Reasonable. If this were for the OP's use, that'd make considerable sense.
If it's for other users, the OP may need to be somewhat more forgiving.

As regards the =IF(SUM(C4:C500)<0,AVERAGE(C4:C500),"") I submitted.

Your suggestion of =IF(COUNT(C4:C500),AVERAGE(C4:C500),"") serves no good
purpose either, as it allows for non-numerics in the column.

Guess what? SUM and AVERAGE ignore nonnumeric cells too (as long as they
don't evaluate to errors). So your point is?

Let us consider the C4:C500. Let us work with a smaller range, A1:A10 and
name it DATA. Let us put the values 1,2,3,4,5,-1,-2,-3,-4,-5 in DATA, and
play with the values.

Good example. COUNT(DATA) = 10, SUM(DATA) = 0, AVERAGE(DATA) = 0. You seem
to be arguing that in this case it'd be a good thing to return "" rather
than the actual average 0. Why?

I propose this formula which handles blanks, negative numbers, 0's,
positive numbers and non-blanks in DATA:

=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),
IF((SUM(DATA)<0)+COUNTIF(DATA,"<0"),AVERAGE(DATA ),""),
"Houston, we have a problem")

The first IF adds something new: checking that all cells either contain
numbers are blank OR evaluate to "", since COUNTBLANK includes cells
evaluating to "" in its resulting count. Not unreasonable.

The second IF test fails to average all numberic cells evaluating to 0, in
which case the average is 0.

If you want error values returned if there are error values in DATA, you
want any cells evaluating to text other than "" to produce a diagnostic
message, you want "" if all cells are blank or evaluate to "", and you want
the average otherwise, try

=IF(COUNTIF(DATA,"=?*"),"trouble",IF(COUNT(DATA),A VERAGE(DATA),""))

This will calculate the average if DATA contains any boolean values along
with numbers, blanks and ""s. If you also want boolean values to trigger the
diagnostic message,

=IF(OR(COUNTIF(DATA,{"=?*";"TRUE";"FALSE"})),"trou ble",
IF(COUNT(DATA),AVERAGE(DATA),""))

As I stated before, COUNT is the appropriate test. The COUNTIF test in my
formulas above tests for an additional condition of 'invalid', but nonerror
values in DATA. Using SUM in the test is *ALWAYS* a bad idea.

We can put this whole issue to rest with:

=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),IF(COU NT(DATA),AVERAGE(DATA),""),"Houston,
we have a problem")

Dave Thomas" wrote...
We can put this whole issue to rest with:

=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),
IF(COUNT(DATA),AVERAGE(DATA),""),
"Houston, we have a problem")

You could if you want to call COUNT twice. Wasteful if DATA is large.

Actually, using COUNTIF as first condition is suboptimal because it'd count
all problem cells rather than stopping at the first one. Better to return
error messages ASAP.

=IF(COUNT(MATCH("?*",DATA,0)),"invalid entries",
IF(COUNT(DATA),AVERAGE(DATA),""))

A well designed formula implementation would not do the COUNT(DATA) twice.
It would be smart enough to use the result from the first COUNT(DATA).

"Harlan Grove" wrote in message
...
Dave Thomas" wrote...
We can put this whole issue to rest with:

=IF(COUNT(DATA)+COUNTBLANK(DATA)=ROWS(DATA),
IF(COUNT(DATA),AVERAGE(DATA),""),
"Houston, we have a problem")

You could if you want to call COUNT twice. Wasteful if DATA is large.

Actually, using COUNTIF as first condition is suboptimal because it'd
count all problem cells rather than stopping at the first one. Better to
return error messages ASAP.

=IF(COUNT(MATCH("?*",DATA,0)),"invalid entries",
IF(COUNT(DATA),AVERAGE(DATA),""))

"Dave Thomas" wrote...
A well designed formula implementation would not do the COUNT(DATA) twice.
It would be smart enough to use the result from the first COUNT(DATA).
....

Like my formula?

Or do you mean the way Excel evaluates formulas in some fantasized universe
in which Excel works differently than it does, has ever done and very likely
ever will do?

Excel calculates every instance of COUNT(DATA) or any function call in the
same formula. There's no behind the scenes common subexpression elimination.
You should adapt your formulas to how Excel actually works.

I find it very hard to believe that Excel which is designed to handle huge
volumes, would not take advantage of simply keeping the results from one
function call to use in another identical function call. BTW, your formula
does not catch the presence of logical values which should not be there if
range is supposed to be numerics or blanks.
And also why use "?*" when "*" should do.

"Harlan Grove" wrote in message
...
"Dave Thomas" wrote...
A well designed formula implementation would not do the COUNT(DATA) twice.
It would be smart enough to use the result from the first COUNT(DATA).
...

Like my formula?

Or do you mean the way Excel evaluates formulas in some fantasized
universe in which Excel works differently than it does, has ever done and
very likely ever will do?

Excel calculates every instance of COUNT(DATA) or any function call in the
same formula. There's no behind the scenes common subexpression
elimination.
You should adapt your formulas to how Excel actually works.

"Dave Thomas" wrote...
I find it very hard to believe that Excel which is designed to handle huge
volumes, would not take advantage of simply keeping the results from one
function call to use in another identical function call. . . .

Get used to it.

Excel formulas represent a simple interpreted language, with the stress on
SIMPLE. You can test this for yourself by using macros to repeatedly
recalculate ranges containing formulas like

=COUNT(DATA)+COUNT(DATA)

vs

=2*COUNT(DATA)

But if you believe I'm wrong about this, then just wait a few minutes for
someone else to show how I'm wrong about this. Just don't hold your breath.

. . . BTW, your formula does not catch the presence of logical values
which should not be there if range is supposed to be numerics or blanks.

Fine.

=IF(COUNT(MATCH({"?*",TRUE,FALSE},DATA,0)),"invali d entries",
IF(COUNT(DATA),AVERAGE(DATA),""))

And also why use "?*" when "*" should do.
....

Because I was making my formula mimick yours.

As I've already tried to point out to you, COUNTBLANK(DATA) includes not
only truly blank cells (those for which ISBLANK returns TRUE) but also cells
evaluating to "" in its result. Matching "*" would treat cells evaluating to
"" as invalid, so UNLIKE your formula, whereas matching "?*" will match TEXT
containing ONE OR MORE characters.

If YOU want to restrict DATA to contain only numbers and truly blank cells,
you need to use either

COUNT(DATA)=COUNTA(DATA)

or

COUNT(DATA)+COUNTIF(DATA,"=")=ROWS(DATA)

but note that the latter requires that DATA span a single column.