Array Calculation Error
 

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile: http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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

works for me

--

HTH

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


"Tobro88" wrote in
message ...

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile:
http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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"))
--
HTH,

RD

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

"Tobro88" wrote in
message ...

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile:
http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile: http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tobro88" wrote in
message ...

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile:
http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile: http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

"Ragdyer" wrote in message
...

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

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.

Array Calculation Error
 

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tobro88" wrote in
message ...

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


--
Tobro88
------------------------------------------------------------------------
Tobro88's Profile:
http://www.excelforum.com/member.php...o&userid=28895
View this thread: http://www.excelforum.com/showthread...hreadid=490591

Array Calculation Error
 

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.
--
Regards,

RD

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

"Bob Phillips" wrote in message
...
"Ragdyer" wrote in message
...

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

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.

Array Calculation Error
 

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

"Ragdyer" wrote in message
...
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.
--
Regards,

RD

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

"Bob Phillips" wrote in message
...
"Ragdyer" wrote in message
...

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

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.

Array Calculation Error
 

"Ragdyer" wrote in message
...

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.

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

Array Calculation Error
 

"Biff" wrote in message
...

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?

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

Array Calculation Error
 

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

Regards,

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

"Bob Phillips" wrote in message
...

"Ragdyer" wrote in message
...

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.

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