Array Problem

[Bob]

Hi,

I can't get my array formula to work properly. I see this problem in both
Excel 2003 and 2007.

The data fields are as follows:

A1: 1
A2: 2000
B1: GB
B2: MB

The formula is: {=SUM(IF(B1:B2="MB",A1:A2/1000,A1:A2))}

The result is: 3 (as it should be)

But if I should change the values in cell A1:A2 from numeric's to text, the
result should change to zero as text can not be added. But this is not what
happens. Instead, cell A1 is not added (as it should not be), but cell A2
is somehow treated as a numeric and is added to the result!

Example:

A1: '1
A2: '2000

(note the apostrophe thus making these cells text).

The result here is: 2 (it should be zero)


Any idea what I'm doing wrong?

 

[Chip Pearson]

The COUNT function will return the count of numbers (not text) in a range.
Thus, you could use COUNT to ensure that both A1 and B1 are numbers. If so,
use your existing formula, otherwise, return 0.

For example,

=IF(COUNT(A1:A2)<>2,0,SUM(IF(B1:B2="MB",A1:A2/1000,A1:A2)))

entered as an array formula, of course.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Sandy Mann]

By preforming arithmetic on A, (ie the divide by 1000), the text number in
A2 is changed into a real number for the division operation.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[kounoike]

How about adding a check if A1:A2 is a number like.
{=SUM(IF((B1:B2="MB")*ISNUMBER(A1:A2),A1:A2/1000,A1:A2))}

keiji

 

[Bob]

That fixed it! Thanks to all for the great tips and suggestions!