Growth function that get known Y's from two columns?

[Guar]

Hi!

I have two columns filled with values, let's say A and B. I am quite familiar with the growth function, but only when it uses data from one column. Is it possible to make it do something like this?

Example: =Growth(A1:A19&B20)

Thank you!

[MrShorty]

I'm not exactly sure what you mean.

According to the help files, if you only give the GROWTH() function one column/argument (for known_y), it assumes the array {1,2,3,...} for the known_x and new_x arguments and the value of TRUE for the constant argument. Specifying these optional arguments should be as easy as including them in the function call.

[Guar]

I'm not exactly sure what you mean.

According to the help files, if you only give the GROWTH() function one column/argument (for known_y), it assumes the array {1,2,3,...} for the known_x and new_x arguments and the value of TRUE for the constant argument. Specifying these optional arguments should be as easy as including them in the function call.

Hello, and thank you.

I leave out the known_x and new_x arguments, because the problem is in the known_y argument.

Instead of just one column being the known_y, I want one column + one cell in the next column, like this:

A | B
-----
Y | -
Y | -
Y | -
Y | -
Y | -
Y | -
Y | -
- | Y

So the growth-formula should include all Y's in column A, as well as the Y in column B. I hope that make things a bit clearer.

[MrShorty]

I know a lot of people are not fond of using helper cells. IMO, however, the easiest way to do this will be to use a helper column that will scan through A and B, locate the "Y" values as you've shown them, and put them into a single column. Then it will be easy to use the GROWTH function on that column. Assuming the layout is similar to what you want, column D could have =A1. Copy that down to D19. Then D20 =B20. Then your =GROWTH(D1:D20).

[Guar]

I know a lot of people are not fond of using helper cells. IMO, however, the easiest way to do this will be to use a helper column that will scan through A and B, locate the "Y" values as you've shown them, and put them into a single column. Then it will be easy to use the GROWTH function on that column. Assuming the layout is similar to what you want, column D could have =A1. Copy that down to D19. Then D20 =B20. Then your =GROWTH(D1:D20).

Ok, maybe I should have mentioned this earlier, but I have to be able to ctrl-down the formula. The Y's in my example are only part of the first growth-function. In reality, the values in A and B go down a lot further. So, what I want is a column C with a Growth-formula that starts in row 10 for example (using the values from row 1 to 10), and ends in row 1000 or so.

[Guar]

Looks like the Growth-function doesn't accept non contiguous ranges as input for the known_y argument.

If anyone can think of a solution, I would be most grateful!

[MrShorty]

Correct, it doesn't like a non-contiguous range. So the obvious solution is to take your non-contiguous range and make it contiguous. The suggestion I made was one way, but it does require you to change the references manually when needed. Coming up with a more automatic solution would require more information about your data: specifically, why is some of the data in column B and some in column A? Is there a pattern that can be tested for?

For example, if I infer from your sample table that, when the data is in A, B is always blank, an when the data is in B then A will always be blank, a possible formula for D would be =sum(A1:B1) copied down. Excel will treat the blank cells as 0, and column D will contain the numbers from A and B. Then you can use column D in the GROWTH function. But, it only works when there is data in column A or B, never both.

[Guar]

The data is like this:

- | A | B | C
- | ---------
1 | Y | - | -
2 | Y | - | -
3 | Y | - | -
4 | Y | - | -
5 | Y | - | -
6 | Y | Y | =Growth(A1:A5&B6)
7 | Y | Y | =Growth(A2:A6&B7)
8 | Y | Y | =Growth(A3:A7&B8)
9 | Y | Y | =Growth(A4:A8&B9)

This goes on for a couple of thousand rows.

[MrShorty]

I'm afraid I haven't got a good solution for that -- it's not the kind of formula I tend to build.

To get you started, perhaps something like in the sample spreadsheet will help you get started. Basically I used the TRANSPOSE() function to put a copy of the input data at the top of the spreadsheet. The INDEX() function is used to build the input array for each growth function on the row where the growth function will be placed. Then the offset function inside the growth function to tell the growth function how many cells to include as its input.

I expect someone else here is better at building these kind of functions and could do the job more elegantly.