HELP !! - stinkin' zeroes

[Bill Lane]

I surrender.
I have a spreadsheet with typical setup - a row for each day of the month,
with the data in typical columns.
The data is linked from another *.xls sheet, and naturally has zeroes
replacing the blank spaces from the sheet I'm linking from.
I need to do an average, max, min, and count on the columns, but can't get
any expression to ignore the zeroes.
I've tried about everything I could find on the web, but keep getting either
the #name? or value errors.
Is it something regarding linked data that I'm unaware of??
I'm hoping someone here can give me an expression that works, and quickly.

TIA,
Bill Lane

 

[Peo Sjoblom]

You could change the links to blanks instead of zeros

instead of

='My Sheet'!A1

use

=IF('My Sheet'!A1="","",'My Sheet'!A1)

or in the average formula use either

=SUM(Range)/COUNTIF(Range,"<>0")

or

=AVERAGE(IF(Range<>0,Range))

the latter
entered with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Bill Lane]

Peo,
Results of your suggestions, using a data range of cells e11:e41, all cells
contain either a positive number or else the "imported " blanks::This yields a "too many arguments for this function" error
This yileds a result of zero, in every case (entered as an array function
with ctl-shift-enter)

You could change the links to blanks instead of zeros
instead of ='My Sheet'!A1
use =IF('My Sheet'!A1="","",'My Sheet'!A1)

This would be the ideal solution (just whack the zeroes), but I'm not sure
where or how to apply this syntax. Maybe you could expound a bit??
Please forgive my ignorance, I normally deal with databases (not Access),
where I can pretty much do whatever I want. The data is initially exported
from the database to an*.xls file (with blanks), then linked to a
pre-formatted *.xls provided by regulatory agencies. This "change the blanks
to zero on links" thing is a major pain. Like I said, I tried every
workaround I could find, with no results. Conditional formatting does not
stop this link/zero behavior, either. Enclosing the literal range in
parentheses does not help either.

bill

 

[Frank Kabel]

Hi
are you sure your data are real numbers (and not numbers stored as
'Text')
Both formulas should work without an error

 

[Bill Lane]

Yep - clicking on the cells in the source sheet reveals actual values.
This link/zero behavior seems to be pretty deeply embedded in Excel - how do
you guys put up with it??

bill

 

[Frank Kabel]

Hi
always text imported values, use conversion functions, etc.
just a matter of experience I think