Finding difference among multiple constant columns and one variable column

Hello,

I apologize if the title is a bit confusing but hopefully I can clear it up. Essentially what I'm looking to do is the following:

There are 4 machines (A, B, C, and D) that have different target production rates and the machines operate at times 1:00, 2:00, 3:00....10:00. Only one machine is running at a time. The operator fills in the actual production that occurs on a sheet which also contains the targets for each machine. I would like to find the difference between the target production and the actual production of the machines without having to change the formula in the 'Difference' column every time the machine changes.

You'll see in the attached spreadsheet that 'Difference' is calculated by subtracting the target from the actual and varies by subtracting F-(A or B or C or D) depending on the machine that is running. However, the formula is changed manually each time the machine changes. Is there a way to have it automatically change based on certain conditions, etc? The cells have been color-coded to identify which machine is running during a specific time frame.

Thanks in advance and let me know if I should elaborate!

Sample.xlsx

No, sorry for misleading you!

The machines don't always change after 10 hours. They can run for any amount of time (2, 3, 14, etc) before changing. Also, there is no sequence between machines - it's entirely random!

Attached is an updated version reflecting the "new" conditions. Thanks for the welcome and speedy response!

Sample_Updated.xlsx

What I initially thought could work would be a simple formula such as 'Difference' = F - A - B - C - D. Then you would just delete the values of target production that you are not using.

For example, G3 = F3 - A3 - B3 - C3 - D3. Delete A3, B3, and D3 (because you aren't using those machines) and thus you would have G3 = F3-C3 = -1. This works in theory but isn't pragmatic with large data sets.

I was hoping there would be an easier way to identify which machine is being used and have a formula based on that identification. Would it be possible to highlight cells with a certain fill color and then have the formula calculate based on which cells are colored? That's the kind of direction I'm thinking but am definitely open to any suggestions.

Alright, I think we're on the same page now. Currently the operators simply circle the machine so I've attached an updated file with a column identifying which machine is in use.

I went ahead and wrote an IF function for the calculation which makes use of the new column 'Machine'.

'Difference' =IF(G3="A", F3-B3, IF(G3="B", F3-C3, IF(G3="C",F3-D3, F3-E3)))

It works, but is not very eloquent. Any cleaner solution?


Thanks!

Sample_Updated2.xlsx