Array Calculation Error

[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

[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)


"Tobro88" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

RD

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

"Tobro88" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

[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


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tobro88" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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

[Bob Phillips]

"Ragdyer" <[email protected]> wrote in message
news:%23BrVsVR%[email protected]...

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

[Ragdyer]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

RD

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

"Bob Phillips" <[email protected]> wrote in message
news:OVKTiXS%[email protected]...
> "Ragdyer" <[email protected]> wrote in message
> news:%23BrVsVR%[email protected]...
>
> > 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.
>
>

[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

"Ragdyer" <[email protected]> wrote in message
news:e99wQeU%[email protected]...
> 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" <[email protected]> wrote in message
> news:OVKTiXS%[email protected]...
>> "Ragdyer" <[email protected]> wrote in message
>> news:%23BrVsVR%[email protected]...
>>
>> > 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.
>>
>>
>

[Bob Phillips]

"Ragdyer" <[email protected]> wrote in message
news:e99wQeU%[email protected]...

> 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>

[Bob Phillips]

"Biff" <[email protected]> wrote in message
news:u%23dm3vU%[email protected]...
>
> 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!

[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 !
----------------------------------------------------------------------------
-------------------

"Bob Phillips" <[email protected]> wrote in message
news:usxD1lY%[email protected]...

"Ragdyer" <[email protected]> wrote in message
news:e99wQeU%[email protected]...

> 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>