The above image is created based on the below question, The range C5:J12 represe

Question

The above image is created based on the below question,

The range C5:J12 represents the squares of an 8 by 8 chess board, with the column numbers of the squares given in C4:J4, and row numbers in B5:B12. Suppose a king is to be placed in the square, whose row number is input in cell B2, and column number in C2. A formula is entered in cell C5 and copied to all the other cells of C5:J12, such that the output would be: a zero in the cell to be occupied by the king, a 1 in each of the cells that can be moved into by the king from the given position, and a -1 in all other cells. A king can move exactly one square, horizontally, vertically, or diagonally.

I created the above using formulas as below,

cell C5==AND(OR(ABS($B$2-$B5)=0,ABS($B$2-$B5)=1),OR(ABS($C$2-C$4)=0,ABS($C$2-C$4)=1)) and copied across.

Using Conditional formatting do the following,

a. All false values should be formatted as -1

b. All true values as 1 but the cell value coreesponding B2,C2 as 0.

2 4 4 1 2 345 6 7 1 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 2 FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE 3 FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE 4 FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE 5 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 6 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 7 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 8 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE 10 12 1A 14

Explanation / Answer

Screen Shot:

Answer:

The formulae in Cell C5 is changed as below:

C5==IF(AND(OR(ABS($B$2-$B5)=0,ABS($B$2-$B5)=1),OR(ABS($C$2-C$4)=0,ABS($C$2-C$4)=1))=FALSE,-1,IF(AND(ABS($B$2-$B5)=0,ABS($C$2-C$4)=0)=FALSE,1,0))

This formula needs to be applied on the range C5:J12.

The conditional logic applies as follows:

1. The first IF logic checks if the value is "False" and if that is true, changes the value to -1.

2. The false part of the first IF statement has another IF that checks if both the cell matches with the number entered in B2 and C2 and if matched, makes the value as "0" and if does'nt match, makes the value "1".

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.