Array Calculation Error

T

Tobro88

I was wondering if anyone could help me with an error when I try to
count scores that fall between a 0% - 67% range. If I leave it as 0%
it does not count the cell. If I change the formula to 0.1% - 67% then
it counts it. Any ideas?

Here is the array in question:
=SUM((I$6:I$28>=0.1%)*((I$6:I$28<=67%)*((C$6:C$28="Asian"))*1))

I need it to accept the 0.1% as 0%.

Thanks,
Tobro
 
B

Bob Phillips

=SUM((I$6:I$28>=0)*(I$6:I$28<=67%)*(C$6:C$28="Asian"))

works for me

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Ragdyer

Are you entering it as an *array* formula, using CSE?

Also ... Any reason you can't use a non-array option?

=SUMPRODUCT((I$6:I$28>=0%)*(I$6:I$28<=67%)*(C$6:C$28="Asian"))
 
T

Tobro88

I am not sure what CSE means so I assume that I am not using it. Like
many people I have had the data collection duties thrown in my lap. I
am learning on the fly. The array that you gave me with the plain 0
seemed to work. Can I use the *array* the rest of the way, or do you
forsee problems?
Thanks for your help!! I really appreciate it!
Todd
 
R

Ragdyer

My automated explanation:
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
Also, each time the formula is revised, it must be entered using CSE.

NOW ... we're talking semantics here.
Array formulas and data Arrays.

It's usually wiser to try not to use array formulas where possible, since
they tend to use more of XLs computing capability.

Check out this link of Chip Pearson's for further info:

http://www.cpearson.com/excel/array.htm
 
T

Tobro88

I guess that I was using CSE :) ! So, I assume that it should be safe
to use unless I have a sizable sheet? Thanks for your help and the
link!!
Todd
 
B

Bob Phillips

It's usually wiser to try not to use array formulas where possible, since
they tend to use more of XLs computing capability.
Click to expand...

All of the formulae that use array ranges, whether that be in array
formulae, or as arrays in a non-array formula, use a lot of resources.

I did some timings for my SP page (which I still need to post), and I found
that SP using a double-unary operator was the quickest SP type, and was
quicker than a SUM(IF((cond1)*(cond2), data), but other SP types (*,
N,1*,+0) were as slow or slower. And surprisingl;y, the array type
SUM(IF(cond1,IF(cond2,data)) was the fastest of all.
 
R

Ragdyer

If you were properly entering the formula, how do you explain the incorrect
return when using 0%?
 
R

Ragdyer

In the past couple of months I've been made aware of numerous fallacies
concerning the "old wives tales" of the efficiency of various procedures and
functions, whether they pertain to arrays OR volatility.

I would venture to say that the vast majority of threads in these groups are
primarily relating to projects of *not* gigantic proportions, so that the
actual capacity and/or capability of XL never really becomes a matter of
significant importance ... EXCEPT among the responders themselves, for their
own enlightenment and/or edification.

Therefore, what you've just mentioned doesn't really surprise me at all.

My own personal involvement in XL efficiency has been limited to a solitary
function mix of replacing Vlookup with Index & Match, where a 40,000 to
50,000 row dB WB was vastly improved by the revision.

Everything else pertaining to speed and efficiency I've picked up here
(whether true or false), within these groups.

Therefore I would tend to believe that the main goal in putting forward
suggested solutions would (should) be clarity and simplicity and
understandability (is that a word?<g>), and let the efficiency syndrome take
a back seat, except when either expressly mentioned, or insinuated, as part
of the actual request.
 
B

Biff

snip >>>>>>>>>>>>>>>

I would venture to say that the vast majority of threads in these groups are
primarily relating to projects of *not* gigantic proportions, so that the
actual capacity and/or capability of XL never really becomes a matter of
significant importance ... EXCEPT among the responders themselves, for their
own enlightenment and/or edification.

snip >>>>>>>>>>>>>>>

I would bet that your assessment is correct!

My biggest "pet peeve":

=LOOKUP(9.99999999999999E+307,................)

Who has time to count all those 9's and make sure you're using just the
right amount?

=LOOKUP(MAX(A:A)+1,...............)

Or, if you know the max value WILL NEVER be >1000

=LOOKUP(1000,...............)

Biff
 
B

Bob Phillips

Therefore I would tend to believe that the main goal in putting forward
suggested solutions would (should) be clarity and simplicity and
understandability (is that a word?<g>), and let the efficiency syndrome take
a back seat, except when either expressly mentioned, or insinuated, as part
of the actual request.
Click to expand...

I agree with you, but it was you who said that array formulae were
inefficient <vbg>
 
B

Bob Phillips

Biff said:
My biggest "pet peeve":

=LOOKUP(9.99999999999999E+307,................)

Who has time to count all those 9's and make sure you're using just the
right amount?
Click to expand...

Once again, I find myself in complete agreement with you Mr Biff!
 
R

RagDyeR

And you converted me by making me have second thoughts!<vbg>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


Therefore I would tend to believe that the main goal in putting forward
suggested solutions would (should) be clarity and simplicity and
understandability (is that a word?<g>), and let the efficiency syndrome take
a back seat, except when either expressly mentioned, or insinuated, as part
of the actual request.
Click to expand...

I agree with you, but it was you who said that array formulae were
inefficient <vbg>
 
Top