Excel array formula

Question

Hi everyoneWhen I enter a formula in a single cell and copy it to the cells below, it computes correctly.&nbsp; However when I force a recalculation (which I have to do as some of the work is dependent on picking up the colour of a cell which Excel does not detect on its own), the formula appears to be modified to an array formula (I think)&nbsp; - indicated by the { } placed around my formula - and the result of all the calculations show as errors (#value).&nbsp; I cannot seem to beat Excel at its game and have my formula left as I entered them.&nbsp; Can anyone shed some light on this problem please.&nbsp;&nbsp;

mathetes · Answer

Sue_L5912&nbsp;&nbsp;Writing a formula that is dependent on picking up the colour of a cell--especially given your qualifier that "Excel doesn't detect [the colour] on its own"--to me sounds like asking for trouble. So some questions:&nbsp;Where does the colour come from in the first place?Is it possible to place a value in an adjacent cell and let that be the reference used by your formula, rather than a colour that "isn't detected by Excel"?

sue_l5912 · Answer

Thank-you for your message mathetes.I have a column where the user can set the colour of individual cells and also enter a value - and&nbsp;I am already using a second column which is populated with a call to my user-function that returns the colour number of the adjacent cell.&nbsp; It is this column that is the problem - it works fine until I recalculate the spreadsheet, then the {} appear around my function and the whole column evaluates to #Value.&nbsp; &nbsp;My end purpose is to calculate the sum of the values grouped by colour code.I have found a work-around and solution by writing a function that deals with the whole array, filters by colour and gives me the total.&nbsp; More concise and no need for additional hidden columns!(I still do not understand how to defeat the problem that Excel has caused by this new design feature!)&nbsp;Thank-you again

mathetes · Answer

Sue_L5912&nbsp;wrote:&nbsp;I have a column where the user can set the colour of individual cells and also enter a value -&nbsp;and&nbsp;I am already using a second column which is populated with a call to my user-function that returns the colour number of the adjacent cell.&nbsp;&nbsp;The user sets the colour: HOW? By picking one manually from the vast array of background colors available?And, [the user also] enters a value: in the cell where the user has also set a color, or in another (perhaps adjacent) cell?Would it be possible to have the color assigned via Conditional Formatting, based on the value entered by the user? &nbsp;Doing so could obviate the need to call a user-defined-function to derive the colour number.&nbsp;Obviously, I don't know the context here, what your relationship is with "the user" is, clearly somebody other than yourself.Is it possible for you to attach a copy of the actual spreadsheet? If the actual contains proprietary or confidential information--as well it might--then a mockup that recreates the problem you're struggling with? Verbal descriptions can be helpful, but have their limits.&nbsp;(I still do not understand how to defeat the problem that Excel has caused by this new design feature!)&nbsp;What is the "new design feature" you refer to? There are array functions newly released, but I don't think that they're what you're talking about (they don't involve the squiggly brackets).

sue_l5912 · Answer

I do not need to further diagnose the problem that I opened this discussion with, as my alternative solution works fine. But to answer your questions : The user picks a colour for the cell from 1 of 5 colours using the standard FillColor menu. The colour indicates a grouping and is also a visual aid and . The coloured cell will have a value entered to denote hours. Then there is a summing of the hours by group (colour). The colour is not dependent on the value entered in the cell.I thought the {} around my function indicated an array function, which I was not intentionally creating - they appeared on their own after a recalc. Perhaps in the 365 version they mean something else...? The design feature is enforcing this apparent array function or whatever they are, and returning an error which doesn't occur when the {} are not there. Pre-365 I would have expected what I was doing to be correct. Perhaps this is another discussion - I will have to spend some time finding out how 365 operates that is different to previous versions.Thank-you for your time.

mathetes · Answer

Sue_L5912&nbsp;&nbsp;OK. I would like to suggest an alternative way to approach that visual aid aspect of picking a colour...This is a method I use in one of my spreadsheets to identify common characteristics of some rows by colour:&nbsp;Since the user has to select something anyway (presumably by taking an action on the toolbar), that same "selecting" can be done by a data-validation drop down (showing the group labels) in a cell next to the cell showing hours, which entry can itself then be used:as the basis for conditional formatting to set colours to achieve that admittedly helpful visual aid, andas the basis for the summing by group--SUMIF is the appropriate function--a method that (I submit) would be both easier and more reliable than relying on a user-defined function to do the grouping.

Forum Discussion

Excel array formula

5 Replies

Resources