Can I use the result of an array calculation inside a regular formula?
 

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

Can I use the result of an array calculation inside a regular formula?
 

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)



wrote in message
ups.com...
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

Can I use the result of an array calculation inside a regular formula?
 

On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

ups.com...

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"&5:D25).

Can I use the result of an array calculation inside a regular formula?
 

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

Can I use the result of an array calculation inside a regular formula?
 

=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy


wrote in message
ups.com...
On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

Can I use the result of an array calculation inside a regular formula?
 

On Feb 18, 9:35 am, "Tom Ogilvy" wrote:
=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy

wrote in message

ups.com...

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

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

Can I use the result of an array calculation inside a regular 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



wrote in message
oups.com...
On Feb 18, 9:35 am, "Tom Ogilvy" wrote:
=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy

wrote in message

ups.com...

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

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

Can I use the result of an array calculation inside a regular formula?
 

On Feb 18, 5:31 pm, "Tom Ogilvy" 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

wrote in message

oups.com...

On Feb 18, 9:35 am, "Tom Ogilvy" wrote:
=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy

wrote in message

roups.com...

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

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!

Can I use the result of an array calculation inside a regular formula?
 

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

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
On Feb 18, 5:31 pm, "Tom Ogilvy" 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

wrote in message

oups.com...

On Feb 18, 9:35 am, "Tom Ogilvy" wrote:
=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy

wrote in message

roups.com...

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

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!

Can I use the result of an array calculation inside a regular formula?
 

On Feb 18, 8:22 pm, "Tom Ogilvy" wrote:
No, sumproduct only handles a small but significant subset of array
formulas.

--
Regards,
Tom Ogilvy

wrote in message

oups.com...



On Feb 18, 5:31 pm, "Tom Ogilvy" 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

wrote in message

groups.com...

On Feb 18, 9:35 am, "Tom Ogilvy" wrote:
=SUM(INDIRECT("D"&MAX(ISNUMBER(SEARCH(B1,A2:A7))*R OW(A2:A7))&":D25"))

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

worked for me.
--
Regards,
Tom Ogilvy

wrote in message

roups.com...

On Feb 18, 8:34 am, wrote:
On Feb 18, 4:49 am, "Bob Phillips" 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)

wrote in message

oups.com...

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"&5:D25).

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

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.