Excel reference cell above

Question

I am trying to get a cell to equal 1 if a referenced cell equals 1, or to equal x+1 if the cell above it equals x, where x is in [0,15], otherwise, I'd like it to equal 0.

I am trying to combine IF/OR statements but I'm not sure how to combine them properly, and the command that is meant to locate the "cell above" --> Address(Row(6)-1, Column(F))=1 is not working.

What is "referenced cell"? What is the relation between "a cell" and "a referenced cell"? — Fumu 7, Feb 12 '16 at 08:08
Screenshots? It's quite hard to understand what you want from this description/ — Tam Coton, Feb 12 '16 at 09:45

score 0 · Answer 1 · answered Feb 12 '16 at 14:34

I'm not sure I've understood all requirements properly but I'd give this a try:

=IF($A$2=1;1;IF(AND(INDIRECT("R(-1)C"; FALSE) >= 0;INDIRECT("R(-1)C"; FALSE) <= 15);INDIRECT("R(-1)C"; FALSE)+1;0))

This formula will return
- 1 if cell $A$2 (the "referenced cell") contains 1
- x+1 if x is the value of the cell one row above the cell containing this formula, if it's value is between 0 and 15
- 0 in all other cases

Excel reference cell above

1 Answers1

Linked