HELP !! - stinkin' zeroes

  • Thread starter Thread starter Bill Lane
  • Start date Start date
B

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
 
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
 
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)
Click to expand...
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
 
Hi
are you sure your data are real numbers (and not numbers stored as
'Text')
Both formulas should work without an error
 
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
 
Hi
always text imported values, use conversion functions, etc. :-)
just a matter of experience I think
 
Back
Top