Finding non zero values in Excel

Finding non zero values in Excel

Didn't find your answer?

One of my colleagues has developed a complex data model in Excel. This returns a table that is populated either by a zero length string or by a text value. We need to be able to test each column to see if there is a value that is not zero length. The columns may contain more than one non-zero value.

I don't mind if the test returns the value, a logical result or anything else. We have tried things like MAXA and COUNTA without success.

I have already observed that it would have been easier in Access, but it's too late for that!
Neil Eglintine

Replies (13)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
20th Nov 2002 11:33

Any good replacing zls with a number first?
Find "" and replace with any number and then use ISTEXT on the result. Worked in a small test file with zls created as Neil suggested.

Thanks (0)
avatar
By AnonymousUser
20th Nov 2002 14:17

Maybe?
If you have the strength ...

{=IF(AND((SUM(ISTEXT(B1:B5)*1)),SUM(LEN(B1:B5))=0),"ZLS only",IF(SUM(ISBLANK(B1:B5)*1)=ROWS(B1:B5),"Blanks only","Non ZLS or number"))}

Thanks (0)
avatar
By neileg
19th Nov 2002 16:53

Tarnished star?
Having used Bob's suggestion, I then find that if you alter the data, the results of the formula don't always update in the way you might expect.

I believe I have had this kind of problem with array formulae before.

I'll work on this to see if I can identify the circumstances.

One thing I have noticed is that if you enter a zero length string as a formula, i.e. ="" this is not treated the same as the value. If you copy ="" and paste it as a value, the array formula suggested by Bob gives a different result.

I knew this was a database application!

Thanks (0)
avatar
By cbales
19th Nov 2002 17:51

Go back to basics
Neil
Taking this situation back to basics, is the problem really any more than conditional testing to see if certain scenarios arise and, if they do, how many times they do?

If not is this really any more complicated than setting up a series of arguements -
(a) within a nested formula (eg - nested Countifs), or
(b) with each arguement as an individual formula on a separate row and then, if a total is required, a total of the answers in each of those rows.

The latter has a big attraction to me in that its simple and the formulae within the tests can be very easily modified or additional ones added. Don't make it any more complicated than it need be.

Countblank will literally only count cells which have no values in them and 0 is a value in the same way thay Zero is a rate of VAT.

EG - for cells A1 to A11:
The number of blank cells wills be =COUNTBLANK(A1:A11)
The number of cells with 0 =COUNTIF(A1:A11,0)
The number of cells with 00 =COUNTIF(A1:A11,00)

Using standard formula facilities will mean that the updates will be automatic and immediate each time the data changes.

Thanks (0)
avatar
By AnonymousUser
19th Nov 2002 18:32

Smacked wrist!
Neil, I can't reproduce the problem you mention, but I am obviously only using a very small amount of test data. The first one I gave using LEN certainly gives less accurate results than the improved one using ISTEXT.

Happy to glance through some real data if you wish and that's practical: mailto:[email protected]

Am I correct to assume that your zero length strings are arising as a result of extracting data from something other than Excel?

Chris is of course 100% right about keeping it easy wherever possible, but anyone still reading this thread (!) is probably looking for something beyond the basics.

Thanks (0)
avatar
By neileg
20th Nov 2002 09:30

Phew!
This one's running!

The colleague that raised this problem has lost interest and gone away!

Chris, I understand what you say, but not how it relates to my original query, since I was try to test for text in a range of zero length strings.

Bob, we were modelling some data that comes from an external system that Excel reads as a zero length string (zls). By this I mean that there is an empty cel but Excel regards this as text. This can be reproduced by setting a cell to contain the formula ="". Then copy this and paste as values. I find that if I use your ISTEXT formula that zls are treated as text, so the formula fails. Obviously Null values will work, but not the zls.

Thanks (0)
avatar
By cbales
20th Nov 2002 14:26

Reasoning was this
Neil
Sorry, I didn't really finish what I meant to say, which was along the lines of count the number of rows where positive tests can be run with standard easy formula methods and then deduct it from the total number of rows.

The result will be the number of "other" rows within the test area. These can of course be highlighted by building in conditional formatting which I find very useful when reviewing large volumes of data - eg all rows with zero's as normal and all other rows as red text on yellow background which makes them stand out a mile.

Alternatively, another way would be to include a test results column in the spreadsheet which, using conditional formulae, would only populate with the non zero results. Using the COUNTA formula should give you the number of rows in the test results column that are populated, but I sometimes have problems with the way that the COUNTA formula works. In which case, I have on more than one occasion circumvented the issue by using COUNTBLANK and deducting the result from the total number of rows.

Thanks (0)
avatar
By AnonymousUser
19th Nov 2002 16:21

But ...
But countif doesn't recognise a zero length string as far as I can see. Likewise countblank doesn't distinguish between a blank and a zero length string. Neil said that counta wasn't doing what he wanted either.

All a bit arcane really! I expect countif and its kindred would do in many cases.

Thanks (0)
avatar
By neileg
19th Nov 2002 09:21

Thanks, Bob
You're a star!

Thanks (0)
avatar
By cbales
18th Nov 2002 13:50

Have you tried ..............
Neil
Have you tried setting up the filter drop down box at the head of each relevant column?

Should do the trick without any difficulty. Select the column header cell or other applicable cell, then select "Data", "Filter". Use the advanced filter to set up your own filtering criteria if not within the standard list provided.

Thanks (0)
avatar
By neileg
18th Nov 2002 15:19

Thanks, Chris
Yes this works, but we want to be able to use the results of the test within the workbook, so the filter approach doesn't really fit.

Thanks (0)
avatar
By AnonymousUser
18th Nov 2002 16:09

Or ...
Better than my last answer:

{=IF(SUM(ISTEXT(B1:B5)*1),1,0)}

See previous posting about entering array formulae (no curly brackets, just Control+Shift+Enter and Excel puts them in)

------------------

Just {=SUM(ISTEXT(B1:B5)*1)} will return the number of cells containing non zero strings, if that's of any additional use?

--------------

If anyone is doing something similar other alternatives are below: use whatever's appropriate to what you are searching the target data for. You can search a range of cells such as A1 to C56, doesn't have to be one row or one column.

ISBLANK
ISERR
ISERROR
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISREF
ISTEXT

IS... formulae return 1 if true, 0 if not -- no need for an extra IF.


Bob

Thanks (0)
avatar
By AnonymousUser
18th Nov 2002 15:41

Will this do?
{=IF(SUM(LEN(B1:B5))>0,1,0)}

This is an array formula (as I'm sure Neil knows, but may be unfamiliar to others): enter it in the formula bar WITHOUT the curly brackets, then press Control+Shift+Enter. Excel makes it curly. Won't work otherwise.

Returns 1 if there is a non zero string in B1:B5 (or B2:B65536, or whatever range you like, but won't work with B:B, dunno why).

Thanks (0)