Excel change referenced cell as a function of a certain value

Question

In an Excel sheet, I would like to change the referenced cells depending on a certain value,

B1=A1 & C1=A1-B1 if D1==1
B1=A1-C1 & C1=A1 if D1==0.

In principle, this can be done by redefining the cells in the following way,

B1=A1*D1+(A1-C1)*(1-D1) & C1=(A1-B1)*D1+A1*(1-D1),

but this generates a circular reference because Excel doesn't know that D1 is either 1 or 0.

Would it be possible to define a certain "switching" function for this case?

Thanks in advance,

Answer 1

The Excel function "IF" allows you to decide what value, either calculated or not, must a given cell be assigned. Writing:

=IF(expression;value_if_true; value_if_false) 

to a cell, allows you to express the logic of your assignment.

Coming to your "dummy" example, your B1 cell contents should look like:

=IF(D1=1;A1;A1-C1)

and your C1 cell contents should look like:

=IF(D1=1;A1-B1;A1)

Should you apply the same assignment style to other cells, remember to lock row or column as needed (by using "$" before the row or column identifier):

=IF($D1=1;$A1-$B1;$A1) 'to lock the column
=IF(D$1=1;A$1-B$1;A$1) 'to lock the row

I hope this may be of some help to you.