I know I can do an array calculation in one cell (for example A1) and
reference A1 in a normal formula. However, is it possible to have the
array formula inside the normal formula? I hope that made sense.

TIA

I think that it is.

Can you give more details of the problem, and we can address it directly
rather than trying to interpret what you are saying.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I know I can do an array calculation in one cell (for example A1) and
> reference A1 in a normal formula. However, is it possible to have the
> array formula inside the normal formula? I hope that made sense.
>
> TIA
>

On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
> I think that it is.
>
> Can you give more details of the problem, and we can address it directly
> rather than trying to interpret what you are saying.
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> <bird_...@my-deja.com> wrote in message
>
> news:(E-Mail Removed)...
>
> >I know I can do an array calculation in one cell (for example A1) and
> > reference A1 in a normal formula. However, is it possible to have the
> > array formula inside the normal formula? I hope that made sense.
>
> > TIA

Ok. I want to use the result of
{=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
string and the result of the is the number 5 in my case). I want to
use this number 5 as part of a range in another formula. For example,
=sum("D"&525).

On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>
>
>
> > I think that it is.
>
> > Can you give more details of the problem, and we can address it directly
> > rather than trying to interpret what you are saying.
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > <bird_...@my-deja.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > >I know I can do an array calculation in one cell (for example A1) and
> > > reference A1 in a normal formula. However, is it possible to have the
> > > array formula inside the normal formula? I hope that made sense.
>
> > > TIA
>
> Ok. I want to use the result of
> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
> string and the result of the is the number 5 in my case). I want to
> use this number 5 as part of a range in another formula. For example,
> =sum("D"&525).

I want the array formula inside the sum formula like this
=sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).

=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))

entered with Ctrl+Shift+Enter since the whole formula must be treated as an
array formula

worked for me.
--
Regards,
Tom Ogilvy


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
>> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>>
>>
>>
>> > I think that it is.
>>
>> > Can you give more details of the problem, and we can address it
>> > directly
>> > rather than trying to interpret what you are saying.
>>
>> > --
>> > ---
>> > HTH
>>
>> > Bob
>>
>> > (there's no email, no snail mail, but somewhere should be gmail in my
>> > addy)
>>
>> > <bird_...@my-deja.com> wrote in message
>>
>> >news:(E-Mail Removed)...
>>
>> > >I know I can do an array calculation in one cell (for example A1) and
>> > > reference A1 in a normal formula. However, is it possible to have
>> > > the
>> > > array formula inside the normal formula? I hope that made sense.
>>
>> > > TIA
>>
>> Ok. I want to use the result of
>> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
>> string and the result of the is the number 5 in my case). I want to
>> use this number 5 as part of a range in another formula. For example,
>> =sum("D"&525).
>
> I want the array formula inside the sum formula like this
> =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).
>

On Feb 18, 9:35 am, "Tom Ogilvy" <twogi...@msn.com> wrote:
> =SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))
>
> entered with Ctrl+Shift+Enter since the whole formula must be treated as an
> array formula
>
> worked for me.
> --
> Regards,
> Tom Ogilvy
>
> <bird_...@my-deja.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
> >> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>
> >> > I think that it is.
>
> >> > Can you give more details of the problem, and we can address it
> >> > directly
> >> > rather than trying to interpret what you are saying.
>
> >> > --
> >> > ---
> >> > HTH
>
> >> > Bob
>
> >> > (there's no email, no snail mail, but somewhere should be gmail in my
> >> > addy)
>
> >> > <bird_...@my-deja.com> wrote in message
>
> >> >news:(E-Mail Removed)...
>
> >> > >I know I can do an array calculation in one cell (for example A1) and
> >> > > reference A1 in a normal formula. However, is it possible to have
> >> > > the
> >> > > array formula inside the normal formula? I hope that made sense.
>
> >> > > TIA
>
> >> Ok. I want to use the result of
> >> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
> >> string and the result of the is the number 5 in my case). I want to
> >> use this number 5 as part of a range in another formula. For example,
> >> =sum("D"&525).
>
> > I want the array formula inside the sum formula like this
> > =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).

So I take it it's not possible to have an array formula inside a
normal formula?

Why would you say that - I just showed you how. If you mean without
entering the whole formuila as an array formula - only the ones that will
work with sumproduct - but in my opinion, sumproduct used this way is an
array formula (just doesn't require array entry).


Not sure what the big hang up is with array formulas.

--
Regards,
Tom Ogilvy



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Feb 18, 9:35 am, "Tom Ogilvy" <twogi...@msn.com> wrote:
>> =SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))
>>
>> entered with Ctrl+Shift+Enter since the whole formula must be treated as
>> an
>> array formula
>>
>> worked for me.
>> --
>> Regards,
>> Tom Ogilvy
>>
>> <bird_...@my-deja.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
>> >> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>>
>> >> > I think that it is.
>>
>> >> > Can you give more details of the problem, and we can address it
>> >> > directly
>> >> > rather than trying to interpret what you are saying.
>>
>> >> > --
>> >> > ---
>> >> > HTH
>>
>> >> > Bob
>>
>> >> > (there's no email, no snail mail, but somewhere should be gmail in
>> >> > my
>> >> > addy)
>>
>> >> > <bird_...@my-deja.com> wrote in message
>>
>> >> >news:(E-Mail Removed)...
>>
>> >> > >I know I can do an array calculation in one cell (for example A1)
>> >> > >and
>> >> > > reference A1 in a normal formula. However, is it possible to have
>> >> > > the
>> >> > > array formula inside the normal formula? I hope that made sense.
>>
>> >> > > TIA
>>
>> >> Ok. I want to use the result of
>> >> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
>> >> string and the result of the is the number 5 in my case). I want to
>> >> use this number 5 as part of a range in another formula. For example,
>> >> =sum("D"&525).
>>
>> > I want the array formula inside the sum formula like this
>> > =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).
>
> So I take it it's not possible to have an array formula inside a
> normal formula?
>

On Feb 18, 5:31 pm, "Tom Ogilvy" <twogi...@msn.com> wrote:
> Why would you say that - I just showed you how. If you mean without
> entering the whole formuila as an array formula - only the ones that will
> work with sumproduct - but in my opinion, sumproduct used this way is an
> array formula (just doesn't require array entry).
>
> Not sure what the big hang up is with array formulas.
>
> --
> Regards,
> Tom Ogilvy
>
> <bird_...@my-deja.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Feb 18, 9:35 am, "Tom Ogilvy" <twogi...@msn.com> wrote:
> >> =SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))
>
> >> entered with Ctrl+Shift+Enter since the whole formula must be treated as
> >> an
> >> array formula
>
> >> worked for me.
> >> --
> >> Regards,
> >> Tom Ogilvy
>
> >> <bird_...@my-deja.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
> >> >> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>
> >> >> > I think that it is.
>
> >> >> > Can you give more details of the problem, and we can address it
> >> >> > directly
> >> >> > rather than trying to interpret what you are saying.
>
> >> >> > --
> >> >> > ---
> >> >> > HTH
>
> >> >> > Bob
>
> >> >> > (there's no email, no snail mail, but somewhere should be gmail in
> >> >> > my
> >> >> > addy)
>
> >> >> > <bird_...@my-deja.com> wrote in message
>
> >> >> >news:(E-Mail Removed)...
>
> >> >> > >I know I can do an array calculation in one cell (for example A1)
> >> >> > >and
> >> >> > > reference A1 in a normal formula. However, is it possible to have
> >> >> > > the
> >> >> > > array formula inside the normal formula? I hope that made sense.
>
> >> >> > > TIA
>
> >> >> Ok. I want to use the result of
> >> >> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
> >> >> string and the result of the is the number 5 in my case). I want to
> >> >> use this number 5 as part of a range in another formula. For example,
> >> >> =sum("D"&525).
>
> >> > I want the array formula inside the sum formula like this
> >> > =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).
>
> > So I take it it's not possible to have an array formula inside a
> > normal formula?

Yes, I mean without entering the whole formula as an array. Can you
show me how to enter the above formula using Sumproduct? I tried it
and it didn't give the correct result.

Thanks!

No, sumproduct only handles a small but significant subset of array
formulas.

--
Regards,
Tom Ogilvy

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Feb 18, 5:31 pm, "Tom Ogilvy" <twogi...@msn.com> wrote:
>> Why would you say that - I just showed you how. If you mean without
>> entering the whole formuila as an array formula - only the ones that will
>> work with sumproduct - but in my opinion, sumproduct used this way is an
>> array formula (just doesn't require array entry).
>>
>> Not sure what the big hang up is with array formulas.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> <bird_...@my-deja.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > On Feb 18, 9:35 am, "Tom Ogilvy" <twogi...@msn.com> wrote:
>> >> =SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))
>>
>> >> entered with Ctrl+Shift+Enter since the whole formula must be treated
>> >> as
>> >> an
>> >> array formula
>>
>> >> worked for me.
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>>
>> >> <bird_...@my-deja.com> wrote in message
>>
>> >>news:(E-Mail Removed)...
>>
>> >> > On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
>> >> >> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>>
>> >> >> > I think that it is.
>>
>> >> >> > Can you give more details of the problem, and we can address it
>> >> >> > directly
>> >> >> > rather than trying to interpret what you are saying.
>>
>> >> >> > --
>> >> >> > ---
>> >> >> > HTH
>>
>> >> >> > Bob
>>
>> >> >> > (there's no email, no snail mail, but somewhere should be gmail
>> >> >> > in
>> >> >> > my
>> >> >> > addy)
>>
>> >> >> > <bird_...@my-deja.com> wrote in message
>>
>> >> >> >news:(E-Mail Removed)...
>>
>> >> >> > >I know I can do an array calculation in one cell (for example
>> >> >> > >A1)
>> >> >> > >and
>> >> >> > > reference A1 in a normal formula. However, is it possible to
>> >> >> > > have
>> >> >> > > the
>> >> >> > > array formula inside the normal formula? I hope that made
>> >> >> > > sense.
>>
>> >> >> > > TIA
>>
>> >> >> Ok. I want to use the result of
>> >> >> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
>> >> >> string and the result of the is the number 5 in my case). I want
>> >> >> to
>> >> >> use this number 5 as part of a range in another formula. For
>> >> >> example,
>> >> >> =sum("D"&525).
>>
>> >> > I want the array formula inside the sum formula like this
>> >> > =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).
>>
>> > So I take it it's not possible to have an array formula inside a
>> > normal formula?
>
> Yes, I mean without entering the whole formula as an array. Can you
> show me how to enter the above formula using Sumproduct? I tried it
> and it didn't give the correct result.
>
> Thanks!
>

On Feb 18, 8:22 pm, "Tom Ogilvy" <twogi...@msn.com> wrote:
> No, sumproduct only handles a small but significant subset of array
> formulas.
>
> --
> Regards,
> Tom Ogilvy
>
> <bird_...@my-deja.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Feb 18, 5:31 pm, "Tom Ogilvy" <twogi...@msn.com> wrote:
> >> Why would you say that - I just showed you how. If you mean without
> >> entering the whole formuila as an array formula - only the ones that will
> >> work with sumproduct - but in my opinion, sumproduct used this way is an
> >> array formula (just doesn't require array entry).
>
> >> Not sure what the big hang up is with array formulas.
>
> >> --
> >> Regards,
> >> Tom Ogilvy
>
> >> <bird_...@my-deja.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > On Feb 18, 9:35 am, "Tom Ogilvy" <twogi...@msn.com> wrote:
> >> >> =SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*ROW(A2:A7))&"25"))
>
> >> >> entered with Ctrl+Shift+Enter since the whole formula must be treated
> >> >> as
> >> >> an
> >> >> array formula
>
> >> >> worked for me.
> >> >> --
> >> >> Regards,
> >> >> Tom Ogilvy
>
> >> >> <bird_...@my-deja.com> wrote in message
>
> >> >>news:(E-Mail Removed)...
>
> >> >> > On Feb 18, 8:34 am, bird_...@my-deja.com wrote:
> >> >> >> On Feb 18, 4:49 am, "Bob Phillips" <bob....@xxxx.com> wrote:
>
> >> >> >> > I think that it is.
>
> >> >> >> > Can you give more details of the problem, and we can address it
> >> >> >> > directly
> >> >> >> > rather than trying to interpret what you are saying.
>
> >> >> >> > --
> >> >> >> > ---
> >> >> >> > HTH
>
> >> >> >> > Bob
>
> >> >> >> > (there's no email, no snail mail, but somewhere should be gmail
> >> >> >> > in
> >> >> >> > my
> >> >> >> > addy)
>
> >> >> >> > <bird_...@my-deja.com> wrote in message
>
> >> >> >> >news:(E-Mail Removed)...
>
> >> >> >> > >I know I can do an array calculation in one cell (for example
> >> >> >> > >A1)
> >> >> >> > >and
> >> >> >> > > reference A1 in a normal formula. However, is it possible to
> >> >> >> > > have
> >> >> >> > > the
> >> >> >> > > array formula inside the normal formula? I hope that made
> >> >> >> > > sense.
>
> >> >> >> > > TIA
>
> >> >> >> Ok. I want to use the result of
> >> >> >> {=MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))} (btw B1 is a text
> >> >> >> string and the result of the is the number 5 in my case). I want
> >> >> >> to
> >> >> >> use this number 5 as part of a range in another formula. For
> >> >> >> example,
> >> >> >> =sum("D"&525).
>
> >> >> > I want the array formula inside the sum formula like this
> >> >> > =sum("D"&{MAX((ISNUMBER(SEARCH(B1,A2:A7)))*ROW(A2:A7))}25).
>
> >> > So I take it it's not possible to have an array formula inside a
> >> > normal formula?
>
> > Yes, I mean without entering the whole formula as an array. Can you
> > show me how to enter the above formula using Sumproduct? I tried it
> > and it didn't give the correct result.
>
> > Thanks!- Hide quoted text -
>
> - Show quoted text -

Thanks for the help. I got my formula worked out.