Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to replace the hardcoded references E13:E15 with relative references:

=+SUM(LN(INDIRECT("E13:E15")))

I've tried variations of below, in cell e16, but nothing seems to work:

=+SUM(LN(INDIRECT(ADDRESS(ROW()-3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))))

I'm getting #VALUE! errors. I assume it's because the Array reference isn't quite right but I'm not sure what it should be.

Yes, I've been using ctrl-shift-enter.

share|improve this question
    
why do you need to use INDIRECThere at all? Simply doing =SUM(LN(E13:E15)) will do exactly what you need! –  Peter Albert Jan 24 '13 at 7:21
    
The spreadsheet is generated by an application as a CSV file. The application isn't tracking columns and rows so it can't output E13:E15. Within a table, it know it's rows and columns. Between tables, it doesn't know it's location. It is designed in this manner to be easily extensible. –  BSalita Jan 24 '13 at 14:21

3 Answers 3

up vote 1 down vote accepted

ROW and COLUMN functions return "arrays" even when they are single values, e.g. {3} rather than just 3, and some other functions can't handle that - you can test that by wrapping each ROW and COLUMN function in a SUM function, formula should then work (although your INDEX suggestion is probably simpler)

You can simplify by using INDIRECT with R1C1 notation for relative references, e.g.

=SUMPRODUCT(LN(INDIRECT("R[-3]C:R[-1]C",0)))

Using SUMPRODUCT instead of SUM just avoids "array entry".

share|improve this answer
    
Better in countless ways than my solution. Thanks! –  BSalita Jan 24 '13 at 15:27

I have found a solution; wrap the contents of LN function inside INDEX.

=SUM(LN(INDEX(INDIRECT(ADDRESS(ROW()-3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())),0,0)))

Works great with control-shift-enter.

Can someone explain why INDEX is required? Is it required because it's an array reference?

share|improve this answer
    
The solution works in Excel but doesn't in LibreOffice or OpenOffice spreadsheets. More evidence of how lacking they are for sophisticated formulas. –  BSalita Jan 24 '13 at 11:09
    
Specifically answering your question in this answer - ROW and COLUMN functions return "arrays" even when they are single values, e.g. {3} rather than just 3, and some other functions can't handle that - you can test that by removing INDEX but wrapping each ROW and COLUMN function in a SUM function......BUT I agree with Peter Albert - why do you need INDIRECT at all? If you do need INDIRECT then another way would be to use the R1C1 option in INDIRECT, e.g. =SUMPRODUCT(LN(INDIRECT("R[-3]C:R[-1]C",0))) –  barry houdini Jan 24 '13 at 13:32
    
Barry, you're suggestion of using R1C1 notation is far better than my approach. I just wasn't getting that I could use that notation and didn't know about the SUMPRODUCT trick. If you write it up, I'll make it the answer. The R1C1 notation is superior because it's more concise and easier to read, and it doesn't require use of INDEX(). SUMPRODUCT is superior because it eliminates dependence on control-shift-enter. That was really problematic because I'm generating CSV files which are array reference ignorant. Your post was chockful of better ideas. Thanks. –  BSalita Jan 24 '13 at 15:05
    
OK, I'll post that as an answer...... –  barry houdini Jan 24 '13 at 15:15
    
Yet another way in which your solution is superior is that the use of R1C1 notation seems to resolve incompatibilities with LibreOffice and OpenOffice spreadsheets. They seem to like R1C1 better than the combo of INDIRECT and ADDRESS. Houdini you are. –  BSalita Jan 24 '13 at 15:24

Array reference is correct, given your coordinates.

You're trying to find the sum of a natural logarithm of a cell-range. Shouldn't you be trying to get a natural logarithm of the sum of the cell-range?

Swap SUM and LN in your formula.

share|improve this answer
    
The formula is correct. It calls for the summation of natural logs. Hardcoded, the result is correct, using INDIRECT(ADDRESS()) it shows #VALUE!. I'm not getting what the difference is unless there is something I don't understand about dynamically creating an array formula reference. –  BSalita Jan 24 '13 at 9:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.