+1 IF there is a cell= something

PaperakuZ

New member
Joined
Oct 25, 2017
Messages
1
Reaction score
0
Points
0
I want cell to add 1 if another cell equals 1. But I want the 1st cell to retain the value. So like
A1=IF(A2=1,A1+1,A1)
Is this possible to do?
 
Not without VBA or tampering with the iterative calculation settings (to prevent a circular reference warning), no. Genrally speaking, a cell can contain a formula or a value, but not both.
 
Just spotted this one :eek:
Yes, there is a way, but there are one or two issues to deal with.
My formula goes in A1 as follows: =IF(F1="Reset",0,IF(B1=1,D1+1,A1))
B1 is set to 1 when your wanting to add 1 to A1
D1 carries the formula =A1
F1 has Data Validation allowing "Reset" or "More" to be selected from a list.

Thus far, Excel will complain about circular references. To resolve, enable Iteration in your Workbook calculation settings and SET THE NUMBER OF ALLOWED ITERATIONS to 1 (one) only. (the default is 100).
Then each time you enter 1 in B1, it will add 1 to the value in A1

NB 1. If B1 is set to 1, then the addition of 1 will happen at every re-calculation. If you don't want this to happen, take out the 1 straightaway after each (valid) calculation.
2. Select "Reset" in F1 if you want to clear values and start again.
3. Do be aware that altering iteration settings stops Excel picking up circular references, which can cause errors/distorted calculation. If this concerns you, then a VBA solution might be a better option.
 
Back
Top