Dynamic Growth/Forecast Functions (No VBA)

[wom80]

I am working with a workbook that uses growth and forecast functions. I am not entirely clear on how either of these work. Feel free to use technical jargon when your explanations.

I have a set of numbers that are my 'known y's' and I also have known x's.

The formula is as follows: =IF(ISERROR(GROWTH($A7:$E7,$A$4:$E$4,P$4)),FORECAST(P$4,$A7:$E7,$A$4:$E$4),GROWTH($A7:$E7,$A$4:$E$4, P$4))

What I would like to achieve is maybe have a row with 0s or 1s for each X/Y combination indicating whether or not to include that combination.

Meaning, say I have Y1, Y2, Y3,.Y4,Y5 and respectively X1,..,X5 in columns A through E. Then for each x/y combination I would have a 0 or 1. So if I have a 0 for X5/Y5 (in col E) but a 1 elsewhere , I would want my formula to only consider columns A through D. The number in cell P$4 feeds from another sheet in the workbook and I already have that to adjust accordingly.

 

[wom80]

I am willing to try VBA if that is the only way to achieve my goal? Any pointers, please?

 

[snoopyhr]

Is this what you are after ?


your 0's and 1's is like


1 1 0 1 0 <--------- only will take first upto and including x2 (x1 to x2 and y1 to y2
0r

1 1 1 1 0 <------- will consider the range for x1 to x4 , y1 to y4

that is it is always series of 1's followed by 0's ,if so then you can use a name range like this

for knows Y's

=OFFSET($A$4,3,MATCH(1,$A$3:$E$3,0)-1,,MATCH(0,$A$3:$E$3,0)-1) name it something like knowny
and then refer to it in your formula in replacement of A7 to E7,

Similarly for known X's

=OFFSET($A$4,,MATCH(1,$A$3:$E$3,0)-1,,MATCH(0,$A$3:$E$3,0)-1)

to replace the range A3 to E3. call this knownx

Excel Workbook
	A	B	C	D	E	O	P	Q
1
2	seed control
3	1	1	1	0	0		Resulted Y	Selected X
4	1	2	3	4	5	X's	45.7885697	3
5
6
7	10	30	40	50	60	Y's
Sheet1

#VALUE!

</td></tr></table></td></tr></table>