Formula help

X

xargon

Hi everyone,

I am not a very experienced office user and need some help constructin
a formula.

What I have is a range of cells...and what I want to know is if th
range consists of entries that are all numbers or all text.

Basically, a boolean test to see if the range is either all numeric o
all text.

Any help would be really appreciated.

Thanks,
Panka
 
J

JE McGimpsey

One way:

Assume your range is A1:A10, and you want to test that the range is all
numeric. Array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=AND(ISNUMBER(A1:A10))

To test if all test (array-entered):

=AND(ISTEXT(A1:A10))

To test if either all numbers or all text (array-entered):

=OR(AND(ISNUMBER(A1:A10)),AND(ISTEXT(A1:A10)))
 
X

xargon

Hi,

I tried the IsNumber and IsText. But it does not work on ranges.

For ex, I have

1
2
3
4
5

and the formula: =ISNUMBER(A1:A5). This however always returns fals
:(

Thanks,
Xargo
 
J

JE McGimpsey

First, try a formula that I actually posted. Make sure you array-enter
the formula. See "Enter an array formula" in XL Help if you need help
with array formulae.

Second, if you're really not getting TRUE with =ISNUMBER(A1:A5), then
your "numbers" are actually Text. You can try coercing them to numbers
by choosing a blank cell, copying it, then selecting A1:A5 and choose
Enter/Paste Special, selecting the Add and Values radio buttons.
 
M

Mike A

The AND part of JE's formula is key.

Entering it as an array (range) formula is key.

=AND(ISNUMBER(A1:A10)) Then press <Ctrl><Shift><Enter>

In the formula bar, the formula now reads

{=AND(ISNUMBER(A1:A10))}

This is an array formula. It evaluates each cell in the range, and
logically ANDS the ISNUMBER results together. Any cell in the range
with a 0 value for ISNUMBER (it's not a number) causes the formula
result to be 0, or FALSE.

Ditto for ISTEXT().

Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
M

Mike A

The AND part of JE's formula is key.

Entering it as an array (range) formula is key.

=AND(ISNUMBER(A1:A10)) Then press <Ctrl><Shift><Enter>

In the formula bar, the formula now reads

{=AND(ISNUMBER(A1:A10))}

This is an array formula. It evaluates each cell in the range, and
logically ANDS the ISNUMBER results together. Any cell in the range
with a 0 value for ISNUMBER (it's not a number) causes the formula
result to be 0, or FALSE.

Ditto for ISTEXT().



Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
Top