I hope this image explains the problem (see error in bottom right.) I need to pass B3:D3 plus E4:G4 as a single range to the FORECAST.LINEAR function. I thought making a named range might help, but it looks like that won't do it.
Any way to combine two ranges into a single conceptual range, so it could be passed to a function like this?
2 Answers
Like this? Define bar_1 and bar-2 as separate names, then join them with the range operator.
bar =bar_1:bar_2
Just to help those who want to generalize this, it ONLY works when the ranges have no rows or columns intervening... and... it doesn't work then either. For example, the following would NOT create a "continuous" range in the sense the answer implies happens for this particular question:
A1:C1 and F2:H2 (as in A1:C1:F2:H2 or simply the result Excel uses from that expression, A1:H2)
This example would give two rows and eight columns rather than a single "row" of six cells made from the two non-contiguous ranges. While the TWO rows the answer would produce may work for this problem, even this problem does not get a single row, six cell range.
In a bare worksheet mocked up for experimenting with this, one gets those cells filled with zeros. In a real spreadsheet, those cells might be filled with real (and non-zero, non-null) data. In this problem, setting it up exactly as shown in the pic, everything is cool, kind of, with the answer of 7. (No idea if that is the correct answer, but it seems to satisfy the poster.) But add values to A2:C2 and F1:H1... no change whatever in the result... all seems good, those cells must not be part of the range created... it seems. HOWEVER, start changing the six cells in bar, to 9's perhaps, and no change occurs in the result of 7 until all of the cells have 9's whereupon a #DIV/0! error occurs. Actually useful to investigating this: the moment you enter some other value into one of A2:C2 or F1:H1, it reverts to 7. Using 9's in those cells is interesting too, but off-topic, kind of.
So clearly those OTHER six cells ARE part of the created range (supported also by using F9 on either of the bar elements of that formula which shows {9,9,9,0,0,0;0,0,0,9,9,9} being used by Excel internally in resolving the formula. (With whatever other value ou entered in the other cells if you checked it with them filled.)
As used, FORECAST.LINEAR() is giving that 7 due to using the same range for the x's and y's: the x when any value is being forecast will be the same as the y since the x and y values are identical.
This gives the impression given in the answer. Any changes to the filled cells must give 7, so long as they are numerals anyway. That then makes it look like one has created a real, one row, six cell continuous range rather than the two row, six cell beast that is really there. The #DIV/0! error must come from the functions real world steps in arriving at the answer: if all the values (the all 9's that is, not the two ranges passed to the function) are the same, it cannot produce any x and y pair for a value not in the cells as they will clearly never be any other value than what they all are now. (Given its methodology apparently passes through a stage that divides by a value that is 0 under this circumstance, even using the same value as they all have as the value to forecast the x and y pair for also fails.)
Also, it is not material HOW the range is created. A1:H2 is the same here as A1:C1:F2:H2 and also the same as using the Named Ranges. The simplicity of the example makes it seem like one has succeeded, but one has not.
HOWEVER, if one has a limited number of cells to deal with, one can use:
=CHOOSE({1,2,3,4,5,6},B3,C3,D3,E4,F4,G4) (most any Excel)or
=CHOOSE(SEQUENCE(1,6),B3,C3,D3,E4,F4,G4) with newer Excel'sto create the single row array.
One could even use something like:
=CHOOSE(SEQUENCE(1,6),OFFSET(B3:D3,0,0,1,1),OFFSET(B3:D3,0,1,1,1),OFFSET(B3:D3,0,2,1,1),OFFSET(E4:G4,0,0,1,1),OFFSET(E4:G4,0,1,1,1),OFFSET(E4:G4,0,2,1,1))to have a formula one could calculate inputs to and therefore make it arbitrarily long (lots of cells) using, perhaps, text inputs from another/other cells. INDIRECT() doesn't help, so this would be a way to build it rather than hardcode it.
One could also create a range, a "helper" range to extend the "helper" term, that includes all the pieces and bits in one's chosen way, and then refer to THAT range. One could even "automate" it (if, perhaps, one used dynamic ranges so hardcoding the helper range was difficult to maintain) using an approach like:
=IF(COLUMN()-1<=COUNTA(bar_1),INDEX(bar_1,1,COLUMN()-1),INDEX(bar_2,1,COLUMN()-1-COUNTA(bar_1)))and copied over to include enough cells to hold the entire range. It is presently set for starting in the column the first range does and makes no allowances for skipped columns. So it addresses what's in the pic, basically. Must be copied to each cell in the helper range, not Spilled (it won't Spill as above, but maybe could be modified to, but that would ruin it), and it itself could be referenced dynamically. Also, it won't (probably, not tested) work if ranges overlap though maybe UNIQUE() could help there. That's mostly a 3-D problem, not 2-D though. Since it needs one "whole" formula per cell in the helper range, you cannot build it in a Named Range, near as I can ascertain.
