Which function should I use?

New Contributor

Hello everyone,

 

Suppose we have a cell and call it N. This will have variable content of positive numbers and the idea is to keep it updated regularly.
Now we need another cell, let's call it X, which will add the number entered in cell N with itself (or a different cell).
The value of cell X (or the different cell) must remain, even after having zeroed cell N and, in the case of a new updated value in cell N, add it again.


I'd like to ask if anyone knows something or has already had a similar experience and could help me with a step-by-step guide. It would be greatly appreciated.

 
3 Replies

@OtsukareSama 

You can also refer to your own cell content in a formula with IF(), so you can preserve values. In your example, the number N is in N1, the number X is in X1! Now enter the following formula in cell Z1:

=IF(N1=0,Z1,N1+X1)

Now your value in Z1 remains the same if you reset N1 to zero.

You may need to set Iteration to True in the settings.

@dscheikey Thank you very much. The problem was to activate iterations in the settings. 

=IF(N1=0,X1,N1+X1) "N1 is the cell that is update regularly and X1 is the cell that store values entered in N1"

 Now I have another problem: every time I hit enter, all iterations are updated.
In other words, once I have entered the data and pressed enter, I have to go back to zeroing it, otherwise it will continue to add the non-deleted data seamlessly. There is a solution that allows me to enter a data and then delete it quickly?

 

I already tried something like this, but it only return FALSE in the cell where it's placed and it doesn't do anything in particular

=IF(N1=0,A1,N1=0) "suppose the formula is placed in cell A1 and N1 is the cell that have to be zeroed"

  

@OtsukareSama 

I have not understood exactly what the new problem is. For me, only the edited cells are calculated as usual.

Your formula example cannot work. You cannot influence the content of another cell. So the part marked in red does not work.

=IF(N1=0,A1,N1=0)

For example, you cannot write in cell B1: A1 should be xyz. You have to do that in cell A1.