formula Question

D

danlinksman

If cells 1 thru 7 are each a zero ( 0 ) and cell 8 is a Z how do I write a
formula that returns Z if Zero is not present in every one of the 8 cells.

Example

0 0 0 0 0 0 0 0 Z = Z

0 0 0 0 0 0 0 0 0 = 0

A 0 0 0 0 0 0 0 0 =A
 
G

galimi

Use the following function:

Function nozero(rng)
Application.Volatile True
For Each cl In rng
If cl.Value <> 0 Then
StrBack = StrBack & cl.Value
End If
Next
nozero = StrBack
End Function

http://HelpExcel.com
 
R

Ron Rosenfeld

If cells 1 thru 7 are each a zero ( 0 ) and cell 8 is a Z how do I write a
formula that returns Z if Zero is not present in every one of the 8 cells.

Example

0 0 0 0 0 0 0 0 Z = Z

0 0 0 0 0 0 0 0 0 = 0

A 0 0 0 0 0 0 0 0 =A
Click to expand...

Your question is unclear.

First of all, in your first example, cells 1 thru 7 are 0's, but you are
showing "Z" as the result.

Also, in your third example, even though the first value is "A", you are
returning an "A" and not a "Z".

If your question is really "If I have a series of 0's and only 1 letter, return
0 if there are only 0's but return the letter if there is one letter; and if
there can be only a single letter with all other entries being zero's, then the
answer to that question is the *array-formula*:

=CHAR(MAX(CODE(rng)))

where rng is the cell reference for the range in which the entries are made
(e.g. A1:I1).

To enter an *array-formula*, after typing in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.


--ron
 
Top