Using a formula to specify a cell reference's Row Number

[Royal]

I've been stumped on how to get this to work, and would appreciate any help. For a few different statistical Functions' arguments, I include two ranges (e.g., B1:B15,A1:A15). However, for each range, I need to make one of the Row Numbers -- the one to the right of the colon -- be based on the answer to this simple formula: E5+2. A friend of mine gave me a "possible syntax" that he thought might work (using the FORECAST Function as an example):

=FORECAST(E4-E3+E5,B3:'B'&E5+2,A3:'A'&E5+2)

This doesn't work, but hopefully it'll give someone an idea of what I'm trying to accomplish. I need, in this FORECAST example, for "E5+2" to equal the appropriate Row Number. I imagine this is a lot easier than it seems, but I'm no expert. Thanks in advance for anybody's help.

 

[anandvarma]

watch the following link to get idea of Forecast function at

http://www.youtube.com/ExcelIsFun#p/search/0/Ss5f0mYvxG0

at "Excelisfun" Youtube Channel by Mr. Mike Girvin

 

[VoG]

Take a look at INDIRECT http://www.cpearson.com/excel/indirect.htm

 

[Smitty]

Welcome to the Board!

Take a look at INDIRECT in the helpfile. I don't have time to set the formula up for you right now, but that should do what you want.

HTH,

 

[Royal]

Take a look at INDIRECT http://www.cpearson.com/excel/indirect.htm

Thanks a lot for that link; it was precisely what I needed to read! I wasn't sure what Function I needed to use, if any.

It frankly took me quite a while to fully process what it all meant, but eventually it clicked in my head after staring at the examples. You use quotation marks to enclose what to use exactly as-is, and use ampersands between the quoted text and the cell references.

I succeeded in using the E5+2 formula for cell rows only by assigning that to another cell: D1. So I had to do the following (this is the FORECAST example only):

=FORECAST(E4-E3+E5,INDIRECT("B3:B"&D1),INDIRECT("A3:A"&D1))

Welcome to the Board!

Take a look at INDIRECT in the helpfile. I don't have time to set the formula up for you right now, but that should do what you want.

HTH,

Unless you have a correction or critique for the above, it looks like a mission accomplished now. I appreciate the reference.

 

[jasonb75]

My mind is in weekend mode and probably not functioning properly, but I think this is right

=FORECAST(E4-E3+E5,B3:INDEX(B3:B15,E5+2),A3:INDEX(A3:A15,E5+2))

 

[Royal]

My mind is in weekend mode and probably not functioning properly, but I think this is right

=FORECAST(E4-E3+E5,B3:INDEX(B3:B15,E5+2),A3:INDEX(A3:A15,E5+2))

No; that didn't work at all. It gives me a #REF! error. I'm gonna read about the INDEX Function to see if I can use that as an alternative though.

Anyone else know any alternatives? It would be nice to do this without having to assign E5+2 to another cell for the INDIRECT Function.

 

[jasonb75]

I just noticed my error, you don't need the +2 in the index version.

=FORECAST(E4-E3+E5,B3:INDEX(B3:B15,E5),A3:INDEX(A3:A15,E5))

Note that I've done the example formula for rows 3:15, If your real range is bigger then the formula will need to be adjusted.

For the syntax problem you had with indirect, the addition must be enclosed in paranthesis, INDIRECT("B3:B"&(E5+2))

Hopefully one of those should work for you.

 

[Royal]

I just noticed my error, you don't need the +2 in the index version.

=FORECAST(E4-E3+E5,B3:INDEX(B3:B15,E5),A3:INDEX(A3:A15,E5))

Note that I've done the example formula for rows 3:15, If your real range is bigger then the formula will need to be adjusted.

Omitting the "+2" would defeat the purpose of what I'm doing, because the value of cell E5 must be summed with the number 2.

Like I posted earlier, I'm now using the INDIRECT Function to do this. I assigned the text "E5+2" to cell D1, then used the following syntax:

=FORECAST(E4-E3+E5,INDIRECT("B3:B"&D1),INDIRECT("A3:A"&D1))

This works just fine. I was just wondering if there was a alternative to having to use another cell (D1) as a reference...

For the syntax problem you had with indirect, the addition must be enclosed in paranthesis, INDIRECT("B3:B"&(E5+2))

Hopefully one of those should work for you.

...and so I'm going to try this and see if it works. I didn't think of trying to use an additional set of parenthesis for "E5+2". I'll post again later and let you know if I succeed. Thanks a lot for your input.

 

[Royal]

And here's my second post already!

I just tried that alternative syntax for the INDIRECT Function, and it was successful! Thanks a lot!

Here's exactly what I typed (FORECAST example only):

=FORECAST(E4-E3+E5,INDIRECT("B3:B"&(E5+2)),INDIRECT("A3:A"&(E5+2)))