Array Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 06 Jan 2008 17:22 GMT

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?

Signature

Thanks,
Bob

Reply to this Message

Chip Pearson - 06 Jan 2008 17:43 GMT

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.

Signature

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

> Hi,
>
[quoted text clipped - 27 lines]
>
> Any idea what I'm doing wrong?

Reply to this Message

Sandy Mann - 06 Jan 2008 17:44 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Hi,
>
[quoted text clipped - 27 lines]
>
> Any idea what I'm doing wrong?

Reply to this Message

kounoike - 07 Jan 2008 05:52 GMT

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

> Hi,
>
[quoted text clipped - 27 lines]
>
> Any idea what I'm doing wrong?

Reply to this Message

Bob - 07 Jan 2008 15:43 GMT

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

Signature

Thanks,
Bob

Reply to this Message