Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Array
Thanks for taking the time to read my quesiton.
I typed in the word array into Excel Help and found this item "About array formulas and how to enter them" I am trying to duplicate the first example and cannot Ex: =Average(if(C5:C14="Europe",D5:D14)) I tried something similar to this (diff cell ref) and I get #VALUE Do I need to check off an addin or something? Thanks, Brad |
#2
|
|||
|
|||
They need to be entered with ctrl + shift & enter
select the cell with the formula, press F2, press ctrl + shift & enter at the same time Regards, Peo Sjoblom "Brad" wrote in message ... Thanks for taking the time to read my quesiton. I typed in the word array into Excel Help and found this item "About array formulas and how to enter them" I am trying to duplicate the first example and cannot Ex: =Average(if(C5:C14="Europe",D5:D14)) I tried something similar to this (diff cell ref) and I get #VALUE Do I need to check off an addin or something? Thanks, Brad |
#3
|
|||
|
|||
Perfect!!!
Why? "Peo Sjoblom" wrote: They need to be entered with ctrl + shift & enter select the cell with the formula, press F2, press ctrl + shift & enter at the same time Regards, Peo Sjoblom "Brad" wrote in message ... Thanks for taking the time to read my quesiton. I typed in the word array into Excel Help and found this item "About array formulas and how to enter them" I am trying to duplicate the first example and cannot Ex: =Average(if(C5:C14="Europe",D5:D14)) I tried something similar to this (diff cell ref) and I get #VALUE Do I need to check off an addin or something? Thanks, Brad |
#4
|
|||
|
|||
"Brad" wrote in message ... Perfect!!! Why? Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel Trickster and undisputed king of array-entered formulas, just had an entire book published on that topic. I remember an MVP colloquium in Seattle just before Excel 95 was released, and asking other Excel MVPs for a crisp definition of when array formulas were needed. No one could supply one. (Certainly I couldn't; else I wouldn't have asked.) Quite apart from your situation, array formulas are necessary when you're working with certain worksheet functions such as LINEST, TRANSPOSE, certain matrix algebra functions, and simpler functions such as FREQUENCY. In these cases, array-entry is necessitated because the function is intended to return an array of results, not just a single number. And in that case you have to begin by selecting an array of worksheet cells that conforms to the rows-by-columns range that the function will return. In the situation you describe, array-entry is needed because you're presenting Excel with an array of TRUE or FALSE values that are used to determine whether to submit values in another array (D5:D14) to the AVERAGE function. Because AVERAGE returns a single value, as distinct from, say, LINEST, you're working with a single-cell array formula. Entered normally, with just the Enter key, Excel expects to see a single condition following the IF function [e.g., IF(C5="Europe",] but your formula submits an array of conditions, and using Ctrl-Shift-Enter signals Excel that it needs to evaluate an array of conditions, not just one. Using Tools | Formula Auditing | Evaluate Formula can provide insight into what's going on. So can other particupants in this ng. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 |
#5
|
|||
|
|||
Array
Thanks Conrad,
So basically the {} denote an array is being used in the formula and Excel needs to treat it differently. Brad "Conrad Carlberg" wrote: "Brad" wrote in message ... Perfect!!! Why? Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel Trickster and undisputed king of array-entered formulas, just had an entire book published on that topic. I remember an MVP colloquium in Seattle just before Excel 95 was released, and asking other Excel MVPs for a crisp definition of when array formulas were needed. No one could supply one. (Certainly I couldn't; else I wouldn't have asked.) Quite apart from your situation, array formulas are necessary when you're working with certain worksheet functions such as LINEST, TRANSPOSE, certain matrix algebra functions, and simpler functions such as FREQUENCY. In these cases, array-entry is necessitated because the function is intended to return an array of results, not just a single number. And in that case you have to begin by selecting an array of worksheet cells that conforms to the rows-by-columns range that the function will return. In the situation you describe, array-entry is needed because you're presenting Excel with an array of TRUE or FALSE values that are used to determine whether to submit values in another array (D5:D14) to the AVERAGE function. Because AVERAGE returns a single value, as distinct from, say, LINEST, you're working with a single-cell array formula. Entered normally, with just the Enter key, Excel expects to see a single condition following the IF function [e.g., IF(C5="Europe",] but your formula submits an array of conditions, and using Ctrl-Shift-Enter signals Excel that it needs to evaluate an array of conditions, not just one. Using Tools | Formula Auditing | Evaluate Formula can provide insight into what's going on. So can other particupants in this ng. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 |
#6
|
|||
|
|||
Array
Thanks alot for those can of worms! Peo!
|
#7
|
|||
|
|||
Array
Basically, that's correct, although it's frequently true that the array is
one that must be evaluated to a set of TRUE/FALSE conditions before the surrounding function can return a result. But not always. Here's one that isolates the final part of a string that contains embedded blanks (I think it's from the Bob Umlas Formula Boutique): =RIGHT(A1,MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0)) So if A1 contains Charles Evans Hughes, the array formula would return Hughes. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "Brad" wrote in message ... Thanks Conrad, So basically the {} denote an array is being used in the formula and Excel needs to treat it differently. Brad "Conrad Carlberg" wrote: "Brad" wrote in message ... Perfect!!! Why? Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel Trickster and undisputed king of array-entered formulas, just had an entire book published on that topic. I remember an MVP colloquium in Seattle just before Excel 95 was released, and asking other Excel MVPs for a crisp definition of when array formulas were needed. No one could supply one. (Certainly I couldn't; else I wouldn't have asked.) Quite apart from your situation, array formulas are necessary when you're working with certain worksheet functions such as LINEST, TRANSPOSE, certain matrix algebra functions, and simpler functions such as FREQUENCY. In these cases, array-entry is necessitated because the function is intended to return an array of results, not just a single number. And in that case you have to begin by selecting an array of worksheet cells that conforms to the rows-by-columns range that the function will return. In the situation you describe, array-entry is needed because you're presenting Excel with an array of TRUE or FALSE values that are used to determine whether to submit values in another array (D5:D14) to the AVERAGE function. Because AVERAGE returns a single value, as distinct from, say, LINEST, you're working with a single-cell array formula. Entered normally, with just the Enter key, Excel expects to see a single condition following the IF function [e.g., IF(C5="Europe",] but your formula submits an array of conditions, and using Ctrl-Shift-Enter signals Excel that it needs to evaluate an array of conditions, not just one. Using Tools | Formula Auditing | Evaluate Formula can provide insight into what's going on. So can other particupants in this ng. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 |
#8
|
|||
|
|||
Array
Conrad Carlberg wrote...
.... Oh, man, did you ever just open a can of worms. Bob Umlas, aka the Excel Trickster and undisputed king of array-entered formulas, just had an entire book published on that topic. I remember an MVP colloquium in Seattle just before Excel 95 was released, and asking other Excel MVPs for a crisp definition of when array formulas were needed. No one could supply one. (Certainly I couldn't; else I wouldn't have asked.) A complete answer requires full details in each argument for each built-in function, but certain subsets are known. The following refer to single function calls. In real formulas with multiple function calls, if one of those functions requires array entry, the entire formula requires array entry. Formulas with only arithmetic or string operators, scalar-valued terms and array constants don't require array entry, but they'd only return the top-left entry of the array result. Functions don't require array entry when using array constants but neither derived arrays not multiple cell ranges, e.g., =SUM({1,2}*{10;100}) returns 330 while =SUM(A1:B1*A2:A3) returns #VALUE! entered normally and 330 entered as an array formula when A1:B1 = {1,2} and A2:A3 = {10;100}. LINEST, LOGEST and FREQUENCY, don't require array entry per se. MINVERSE and TRANSPOSE require array entry except when all their arguments are array constants. MMULT requires array entry except when both its arguments are array constants *OR* when its result is a scalar, e.g., with A1:B1 and A2:A3 as above, =MMULT(A1:B1,A2:A3) returns 210 entered normally. However, you'll only get the top-left entry from the array results returned by these functions unless they're entered as multiple cell array formulas. ROW and COLUMN *always* return arrays, even when their arguments are explicit or default references to single cells, and don't on their own require array entry. Same 'however' as the last paragraph. A few built-in functions expect array arguments and don't require array entry, e.g., SUMPRODUCT, LOOKUP, SUMX2MY2, SUMXMY2. Aggregation functions, e.g., SUM, COUNT, AVERAGE, require array entry only when some arguments are derived arrays. All other functions require array entry when some arguments are derived arrays or single area multiple cell ranges (which evaluate to arrays). The type testing functions, e.g., ISNUMBER, ISBLANK, ISERROR, don't require array entry. Same 'however' as previously. I'm intentionally remaining silent on the functions that return range references, INDEX, INDIRECT and OFFSET, or have specific arguments that must be (or are supposed to be) range references, N, T, CELL, SUMIF and COUNTIF. Their semantics are so screwy that they'd need treatises on how the type of each argument affects them. Also, the former set of functions can produce return values that work like arrays of range references, and the latter set are the only functions that can use such results. I'm also remaining silen on the list processing functions, e.g., DSUM, DCOUNT, because I never use them, so I don't care how they behave. There are exceptions to these rules, but you'll have to work to find them. Quite apart from your situation, array formulas are necessary when you're working with certain worksheet functions such as LINEST, TRANSPOSE, certain matrix algebra functions, and simpler functions such as FREQUENCY. . . . .... Not entirely correct. See above. Also, e.g., =SUMPRODUCT(TRANSPOSE({1,2,3}),10^ROW(1:3)) returns 3210. |
#9
|
|||
|
|||
Array
Hi Harlan,
I still don't have a "crisp" definition (g), although I haven't before seen a listing such as you provide. I doubt that a crisp definition exists. I would quibble with LINEST, LOGEST and FREQUENCY, don't require array entry per se. Formally and syntactically, that's true, but as a practical matter array entry is required -- how many times have we seen users in the ngs frustrated because they couldn't seem to get the full results promised by the documentation? As you subsequently point out, However, you'll only get the top-left entry from the array results returned by these functions unless they're entered as multiple cell array formulas. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 |
#10
|
|||
|
|||
Array
Conrad Carlberg wrote...
Hi Harlan, I still don't have a "crisp" definition (g), although I haven't before seen a listing such as you provide. I doubt that a crisp definition exists. I would quibble with LINEST, LOGEST and FREQUENCY, don't require array entry per se. Formally and syntactically, that's true, but as a practical matter array entry is required -- how many times have we seen users in the ngs frustrated because they couldn't seem to get the full results promised by the documentation? As you subsequently point out, .... Failure to read the full documentation on array formulas is whose fault? Microsoft's, which includes the following text in online help for MMULT: "Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A8:B9 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2." Or, just perhaps, the users? As they say in other newsgroups, RTM, and when that doesn't work, RTFM. The need for array entry *should* be obvious when an array result is expected. Finally, there are contexts when LINEST or LOGEST could be used in formulas to produce scalar results. If those formulas only require SUMPRODUCT or similar functions, then the entire formula may not require array entry. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel array formulas | Excel Discussion (Misc queries) | |||
Pass an array to Rank | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |