| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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). |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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). |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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). |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula needed to concatenate text with result from calculation | Excel Discussion (Misc queries) | |||
| Formula Help - Perform a calculation only if the result falls between 2 values | Excel Discussion (Misc queries) | |||
| Table Lookup formula where 2 known values are inside array | New Users to Excel | |||
| formula result #value! needs to equal zero for average calculation | Excel Worksheet Functions | |||
| Array formula weird result | Excel Discussion (Misc queries) | |||
Linear Mode
