Basic MS Excel question.

May 9, 2007 5:11 AM

Basic MS Excel questions. How do I ask for the numeric value of the Nth cell to the right of the cell with this (requested) function in it? Relatedly, is there a way to write a function in some cell that places a numeric value in a cell N cells to the right of that cell?
posted by Mr. Justice to Computers & Internet (3 answers total)

I hope I'm understanding your question right but use the INDEX function.

In my test spreadsheet I put A1 to be your N (i.e. the index of the cell you want to look at.)
B1 contains this function.
C1:G1 is the range of values to the right of the cell

=INDEX(C1:G1,,A1)

Then what ever value you put in A1, the value of that index in the range you defined will be put in B1.

Good Luck!
posted by puddpunk at 5:28 AM on May 9, 2007

You can also use the OFFSET function:

=OFFSET(A1, 0,5)

will return the value that is 0 rows and 5 columns away from cell A1, i.e. cell F1.

From the MS Excel Help file:
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Syntax

OFFSET(reference,rows,cols,height,width)
posted by skwm at 5:58 AM on May 9, 2007

Relatedly, is there a way to write a function in some cell that places a numeric value in a cell N cells to the right of that cell?

Well, you can't do anything with standard formulas that will directly 'put values' into another cell. You could put a formula into that cell (the one N cells to the right) to calculate the value you want. Or you can write a function in VBA that will put a value there (but then I think it stops being a 'basic excel' problem). Can you be more specific about what it is you want to do? There's almost always some way to do whatever you want in Excel.
posted by svenx at 7:03 AM on May 9, 2007