Help with a simple Excel function (I think!)

Help with a simple Excel function (I think!)

Didn't find your answer?

I have a simple spreadsheet showing the value of investments at irregular intervals.  Say that column F shows the value of the investments in March, column E might be in May, C is August and B is December.

I want to calculate the increase in value of the investments across different periods.  So, sometimes I might want to compare the figures in column C vs E, or B vs F or whatever.

To my mind, the easiest way to do this would be to "read in" a column reference which would be used in the calculations. 

So, my spreadsheet would have a cell in which I would manually type in the starting column (say, "E") and a second cell in which I would enter the ending column (say "B") which would then enable me to set up simple calculations, like =(x3-y3), replacing "x" with B and "y" with E.  Just by retyping letters in these two cells I can endlessly change the start and end points for my calculations.

There may be more elegant ways to do this but, as you probably realise, I'm no Excel expert so I'm happy with this somewhat clunky approach.  Trouble is, I cannot describe my intentions in a way that seems to tie up with anything in Excel help!

Replies (4)

Please login or register to join the discussion.

avatar
By Ben Lauritson
16th Dec 2015 16:52

The "INDIRECT" function may help?

Greetings,

If I've interpreted your question correctly then it sounds like the INDIRECT function may be what you're looking for. For example, if the value in Cell A1 happens to be "B2" then the formula "=INDIRECT(A1,TRUE)" would be the same as typing "=B2".

So from what you're saying then, again assuming I've interpreted your question correctly, if your starting column (e.g. "E") is in Cell A1 and your ending column (e.g. "B") is in Cell A2, then the following formula may achieve what you're looking for:

=INDIRECT(A1&"3",TRUE)-INDIRECT(A2&"3",TRUE)

Excel would translate this behind the scenes to "=E3-B3" and give you the result as though that were the formula you had typed in the first place. Of course, if you changed the value in Cell A1 to D instead, the INDIRECT formula would then translate behind the scenes to "=D3-B3" etc.

Does that answer your query?

Thanks (1)
avatar
By WhichTyler
16th Dec 2015 18:27

Or
Use INDEX and MATCH, then you can use the month names...

Thanks (1)
avatar
By waltere
05th Jan 2016 09:22

Many thanks to both of you!

First of all, apologies for not thanking you sooner - I posted this a little while ago but have only just checked back to see if there were any responses.  A little thing called "Christmas" got in the way!

Anyway, INDIRECT is exactly what I need - I'd seen this mentioned elsewhere but never quite understood how to apply it - @Ben - your example is very clear and I was able to apply it to my own spreadsheet almost immediately - thanks.

@WhichTyler - I'll take a look at using INDEX and MATCH to refine the solution, too.

Thanks (1)
avatar
By Ben Lauritson
05th Jan 2016 11:26

Glad I could help :)

Thanks (0)