how to find zeros in a spreadsheet file

[mjaugustin]

I have a spread sheet file having 33 column I want to find  is there any zero in any of the columns,how this can be done easily.searching one dimensional array is time consuming since file size is very large

thanks for your time

regards

augustin

[RavensFan · Accepted solution]

33 columns by how many rows?

What do you want to do when you find it?  Do you just want to know there is one, or do you want to know where it is?

You could do a multiply array elements from the Numeric palette.  If the result is zero, then you know you've got one.

Message Edited by Ravens Fan on 04-21-2010 10:59 PM

[MattBradley]

I like Ravens Fan's solution, but you could also take the absolut value of the entire array, get the minimum, and then compare it to 0.

[johnsold]

If the spreadsheet file is a text file, you could use the string functions to search for the presence of zeros.  The details would depend heavily on the format in which the data is saved.

For working with numeric values another caution is in order.  In this case it probably would work to use the Is Equal to Zero comparison primitive, but remember that in general equality comparisons on floating point numbers are not a good idea due to the finite representation in binary and roundoff errors after calculations.  Adapting Matt Bradley's suggestion you might test whether the absolute value is less than some tolerance, such as 1e-6, rather than zero.

Lynn 

[JÞB]

---

johnsold wrote:

If the spreadsheet file is a text file, you could use the string functions to search for the presence of zeros.  The details would depend heavily on the format in which the data is saved.

For working with numeric values another caution is in order.  In this case it probably would work to use the Is Equal to Zero comparison primitive, but remember that in general equality comparisons on floating point numbers are not a good idea due to the finite representation in binary and roundoff errors after calculations.  Adapting Matt Bradley's suggestion you might test whether the absolute value is less than some tolerance, such as 1e-6, rather than zero.

Lynn 

---

Lynn- good point! but use abs val and compare to the machine epsilon  (that "little e" constant on the numeric>constants sub-palette is the smallest value that the computer can represent as a dbl)

[johnsold]

Jeff,

If the data is being read from a spreadsheet, the tolerance need not be smaller than the resolution in the file.  I agree that machine epsilon is universal.

Lynn 

[mjaugustin]

hi

Thanks for the reply

No of rows will vary

I want to know where it is also

[mjaugustin]

hi

Thanks for your reply,your solution works if only one zero is there if multiple zeros are there how i can find the position of those

[Lucither]

---

mjaugustin wrote:

searching one dimensional array is time consuming since file size is very large

---

If you are going to need the positions of the 0's then you are going to have to search. All the above examples where for quickly assessing if there where any 0's present. I would ascertain first, using the above examples, whether you have any 0's. If you do then search for them.

Rgs,

Lucither

[tbob]

Just a thought.  Why not look for 0 when you are building your spreadsheet file.  Flag it there.