CODEWITHSUNDEEP
excelexcel-formula
Jonaslux
Jonaslux

Reputation: 37

Adding numbers to cell references in Excel

I was asking myself whether it is possible to add numbers to cell references which are used for formulas in Excel.

In my case I have the formula 

    =VAR.P('Excess Return'!E2:E22)

which obviously gives me the variance for the numbers ranging from E2 to E22.

Now I want to drag down the formula for all other cell and therefor i want something like

    =VAR.P('Excess Return'!E2:E22+D3)

with the number 4 in the Cell D3 for example.

Now Excel should do

   =VAR.P('Excess Return'!E2:E26)

but it doesn't and returns "#NAME?"

Any idea on how I can solve this problem?

Any help is greatly appreciated.

EDIT for clarification

My example wasn't really what I actually meant. Sorry for that.

So I have two columns with numbers E and F. In column G there is the formula.

    G2 = VAR.P('Excess Return'!"E"&E2:"E"&F2)
    E2 = 2
    F2 = 22

which then should result in 

    G2 = =VAR.P('Excess Return'!E2:E22)

So I want to insert the numbers stored in column E and F into the cell reference for the variance function.

Upvotes: 1

Views: 549

Answers (1)

Ulli Schmid
Ulli Schmid

Reputation: 1167

  • If the size of your referenced range is not supposed to change while dragging down, you can probably do what you want by properly using relative and absolute cell references

  • If you want to change your reference based on the value of another cell, however, you probably need an indirect reference.

Solving your example with INDIRECT(): 

=VAR.P(INDIRECT("'Excess Return'!E"&E2&":E"&F2))

Another way to reference indirectly - preferable because it is less volatile and causes less recalculations: 

=VAR.P(INDEX('Excess Return'!E:E,E2):INDEX('Excess Return'!E:E,F2))

Thanks @DirkReichel for pointing out that alternative.

Upvotes: 2

Related Questions