Hardware

Ask something
Question Locked

Excel: can you reference cell output for a formula range?

0 helpful
3 replies

by planktonguy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi;
Here is my question. I want to fit a line to an exponential curve in excel, so I can't use the zero values that occur in my column data. In other cells in the spreadsheet I have calculated the row# of the column where my values turn zero, but I don't know how to include this into my formula in another cell.

ie. =-LINEST(LN(Sheet1!N6:N147),Sheet1!M6:M147)

I want to be able to specify the "N147" and "M147" values of the ranges above. I can find the row# using the MATCH function, but can I include it in the above formula?

For instance, I know this doesn't work.
=-LINEST(LN(Sheet1!N6:(_calculated_cellvalue_using_MATCH)),Sheet1!M6:(_calculated_cellvalue_using_another_MATCH)),)

A workaround will do as well, but I would like to know if this thing is even possible. I seem to remember some way of doing this ages ago via CONCANTENATE and EVALUATE, but Evaluate isn't listed at all, so I don't know if this is a false memory or not.

3 replies

by realit

There is a similar feature in Excel 03, try Tools then Formula Auditing to use Evaluate Formula.

by spikeyi

Use INDIRECT in excel 2010, not sure about earlier versions.
INDIRECT extracts a cell reference from text. Use & or CONCATENATE to create a text string e.g. &"M"&(your formula) and then use INDIRECT to reference it.

This discussion is closed to new replies.