How to make 2 cells mutually dependent (or the simplest similar thing)

Hi,


I have the simplest table made of 2 cells and this is what I would like:


-If I put a value in the first cell, the second cell displays that value times 5.

-If I put a value in the second cell, the first cell displays that value divided by 5.


So basically, I would like to be able to edit any of those two cells and always have the second one displaying 5 times the first one.


But I know that numbers doesn't allow a cell to be editable AND have a formula at the same time.

So how could I achieve something similar in the most user friendly way?


Thanks.

Posted on Jul 16, 2023 6:43 AM

Reply
1 reply

Jul 16, 2023 9:22 AM in response to Bayl0ck

The answer depends on whether you will be reusing the same two cells each time or will type a number into one of these cells then move on to the next row for the next time. Both cells need to be formulas. Once you overwrite a formula with an actual number, it is gone.


If you will be using them one time only,

B2 =IF(ISERROR(FORMULATEXT(C2)),OFFSET(B2,0,1)×5,"")

C2 =IF(ISERROR(FORMULATEXT(B2)),OFFSET(C2,0,−1)÷5,"")

Fill down with both to complete the columns.

Type a number into either cell (overwriting the formula) and the other will do its thing.


The use of OFFSET tricks it to not see the circular reference. In reality, it isn't really circular because the part of the IF statement that references the other cell for multiplication/division only gets executed after the other cell's formula has been overwritten with a value.


If you want to reuse the same two cells then you will need to put your input into a third cell so you do not overwrite the formulas. An entry might be something like 5L or 5R to specify which cell gets the "5" and which one gets either 25 or 1/5. One possible solution would be this:


A2 is where you enter the number followed by either an L or R

B2 =IF(RIGHT($A2,1)="L",LEFT($A2,LEN(A$2)−1)×1,LEFT($A2,LEN(A$2)−1)×5)

C2 =IF(RIGHT($A2,1)="R",LEFT($A2,LEN($A2)−1)×1,LEFT($A2,LEN($A2)−1)÷5)


This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

How to make 2 cells mutually dependent (or the simplest similar thing)

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.