2

It appears that Numbers does not have user defined functions so I am writing a rather complicated expression in Emacs and then pasting it into the cell to use as an equation.

I'd like to reference in the expression a cell relative to the cell that the equation is in. Looking at Apple's function list I see functions in the "Reference" section where I can derive another location if I had a location to start with... thus the question:

Is there a function that returns the current cell's location?

It may be that there is a notation rather than a function to refer to either the current cell or a cell relative to the current cell. I'm searching the help for Numbers for that as well.

Edit:

For clarification, here is the equation I'm creating in Emacs:

IF(ISERROR(STOCKH("AAPL", "close", TODAY()−30−0)),
   IF(ISERROR(STOCKH("AAPL", "close", TODAY()−30−1)),
      IF(ISERROR(STOCKH("AAPL", "close", TODAY()−30−2)),
         IF(ISERROR(STOCKH("AAPL", "close", TODAY()−30−3)),
            TODAY()−30−4,
            TODAY()−30−3),
         TODAY()−30−2),
      TODAY()−30−1),
   TODAY()−30−0)

This is finding a date that is approximately one month ago (30 days) where the stock market was open. I try today - 30 (days) and if there is a stock price on that day, then I return today - 30. If the test fails, I try today - 31, today - 32, ... up to today - 34 which covers any three or four day weekend.

I want to do this but replace the 30 with 7 (for a week ago), 91 (for last quarter), 182 for six months ago, and 365 for one year ago.

Rather than compose each of these (which I've done but I'd like a simpler way to do it), I'd like to replace the "30" in the equation above with a relative cell reference such as the cell in the same row as the cell with the equation but in the previous column. I would then put a 30 in that cell in this instance and I would put 7, 91, 182, ... in the referenced cell in the other instances.

To recap, I'd like to just paste the equation I compose in Emacs three or four places and then put a number such as 30 in the cell next to the equation.

Improve this question
7
  • 1
    Try just clicking on the cell, creating a reference? Since references are relative by default, it should still work if you paste/fill the cell to elsewhere.
    – SilverWolf
    Commented Jan 10, 2019 at 17:00
  • Ah.. I see. Paste the equation, fix it, then copy and paste the result to the other locations. I'll try and make that work.
    – pedz
    Commented Jan 10, 2019 at 21:10
  • 1
    What do you mean "paste it, fix it, copy it"? It should Just Work™ if you copy and paste the formula without even modifying it. Or is that what you're doing, and I'm missing something?
    – SilverWolf
    Commented Jan 10, 2019 at 21:16
  • 1
    Ah, I see what you mean. (: It looks like you should do the reference click as your last step -- when you copy it from the formula box, it doesn't update the references, and when you copy the cell without the box open, it will copy the result outside of Numbers (as rich text) but inside Numbers it must be maintaining a separate buffer with formula information. When you paste it elsewhere, it will paste the formula, moving all the references as well.
    – SilverWolf
    Commented Jan 11, 2019 at 2:46
  • 1
    The ROW() and COLUMN() functions evaluate to the current cell's row and column, so you can use those in other functions such as OFFSET in order to create a formula that is immune to being copied and pasted.
    – jetset
    Commented Mar 11, 2019 at 13:10

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .