Need formula when Cell A=X then change Cell B=0

[adamsurpren]

Hi all,

I am trying to make a spreadsheet for my office that we will use to calculate how our day is split up. So my dilemma comes in that I need to make it so that when a Cell = 7.5 another cell will then change from a 1 to a 0.

If you need more detail, please read below. I have also submitted a copy of this spreadsheet to further explain what I am trying to accomplish. Warning, this explanation is long and complicated...

The goal is to take the 37.5 hour work week and after deductions - give an accurate number of hours we actually spent "processing". However, since we are allowed 30 minutes total in break time and another 30 minutes for coffee/bathroom etc, I have added a column (titled Other) that manually deducts a 1 from our 37.5 total - thus making the weekly total only out of 32.5 hours.

Heres how it looks. Columns B, C, D, E, F, and G represent headers such as time off, meetings etc.

Rows 3,4,5,6, and 7 represent Monday through Friday.

Cells G3 to G7 are all defaulted with a "1" as described above.

So if I was to be out all day monday, I would put 7.5 in the time off column. My dilemma comes now in that my 7.5 in time off now adds to the defaulted 1 in the "other" column to make 8.5 hours being taken off from my 32.5 total - which thus represents an inaccurate end of the week total of processing hours. I am essentially cheating myself of one processing hour. Now I know the solution is simple - just delete the 1....but it's not that easy because this spreadsheet has to be used by hundreds of people all within the ages of 18-80...some of them might get confused. The other solution would be to delete the "1" column entirely and make the spreadsheet out of a 32.5 hour week, except that then if you took the day off you would have to put 6.5 - not 7.5, otherwise if you were hypothetically out all week - you'd be at negative 5 processing hours rather than 0. People are so accustomed to 7.5 I dont want to start making them type 6.5, and they want to just type in their time and not be deleting things, that I need a formula so they dont have to.

Thus why I came to the conclusion to keep my spreadsheet as originally designed, except that when someone types a 7.5 in a specific column, it will then automatically change the "other" columns 1 into a 0. This way, nobody has to delete anything, all they need to do is type their time in and the end result will be totally accurate.

I can't think of any other way to make this work as accurate as possible, and I'm very sorry for the long post! If anyone reads this and is able to help me I would be extremely grateful!!!

Thank you very much for your time and help,
Adam