Field of view of surveillance cameras in Excel

Question

Good afternoon Commonwealth,

I have a question about Excel, where I do not know if it is possible to make such a request. My goal is to take a plant and through a Solver, put surveillance cameras at certain points of the plant in order to monitor the entire area (Coverage Problem).

This image represents one of those areas to monitor. The cells that have the value 1 represent the placement of cameras. This is done automatically with Solver, with some mathematical restrictions.

What I’ve been trying to do, and what I can’t do, is make the FOV (field of view) of each camera represented by the value 1 in the adjacent squares.

Example of what I want:

As you can see, the squares adjacent to each camera are filled with the FOV of the corresponding camera, but I was the one who put it to hand. I would like to know if there is a method to elaborate my request automatically, through Visual Basic or formulas, or other method that is not of my knowledge.

This is my master’s work on resource optimization.

Any information you need to help me, I’ll be available.

Thank you.

Answer 1

Hello, You can use the formula SE:

I made your table and noticed that each camera has a range, but the ranges cross, so I inserted in the cells that the ranges cross the following formula:

=SE($D$6 = 1;1;SE($D$3 = 1;1;0))

In my case the cells D6 and D3 are the cameras of the left and they occupy this range:

The same way on the other side.

Ranges that do not cross (peripherals):

I put the formula:

=SE($D$3 = 1;1;0)

Just now you adapt your situation.