how to make 3 cells add up and not exceed specific number in excel

[ibanez7]

Hello everyone
I have an issue which I'm not sure that can be configured in excel and was hoping someone could help me with.
I have 3 cells:
Up Down Idle

Whenever I insert numbers in these 3 cells the numbers should not exceed 10.5 hours in Total.
The UP cell is amount of hours the machine is running. The DOWN cell is the amount of hours the machine is broken or down. The IDLE cell is the amount of time the machine is sitting there not doing anything. Now if you add these numbers the amount cannot exceed 10.5 hours in total (which is the total hours in a shift).
So let's say for example I would have:
UP=4 hours entered DOWN=2.25 hours entered the number in IDLE would automatically change to 4.25
Is there a way to configure this in Microsoft excel for this to work.

I know that I could add another cell and call it TOTAL then add the 3 cells above to be equal to 10.5 in the TOTAL cell and change color let's say RED if it doesn't add up to 10.5 and GREEN if it does equal to 10.5 but is there a way to make this work the way they really want it to work?
=b3+c3+d3 would use conditional formatting, then equal to 10.5 then change to GREEN and if not = 10.5 change to RED.

Thanks for any help with this.

[jeffreybrown]

Whenever I insert numbers in these 3 cells the numbers should not exceed 10.5 hours in Total.

So let's say for example I would have: UP=4 hours entered DOWN=2.25 hours entered the number in IDLE would automatically change to 4.25.

At least to me this seems a little contradictory. You state you will enter numbers in 3 cells, but then you say you want the IDLE to automatically adjust.

Maybe attach a small sample workbook with what you are after.

If UP = 4 and DOWN = 2.25 then 10.5-(B3+C3) would return 4.25 for IDLE in cell D3.

Instead, if UP = 4 and DOWN = 4 then 10.5-(B3+C3) would return 2.5 for IDLE in D3.

All of this equals 10.5

Probably missing it but not sure what you want?

[BenjieLop]

If I understand your post correctly, you enter number of hours for UP and DOWN and you want the IDLE to automatically adjust such that the total (UP + DOWN + IDLE) will always be 10.5.

Based on this understanding, the only formula that you need would be to enter in the IDLE cell would be

=10.5-(A1+B1)

where

A1 = cell for the UP hours
B1 = cell for the DOWN hours

I hope this helps.

Hello everyone
I have an issue which I'm not sure that can be configured in excel and was hoping someone could help me with.
I have 3 cells:
Up Down Idle

Whenever I insert numbers in these 3 cells the numbers should not exceed 10.5 hours in Total.
The UP cell is amount of hours the machine is running. The DOWN cell is the amount of hours the machine is broken or down. The IDLE cell is the amount of time the machine is sitting there not doing anything. Now if you add these numbers the amount cannot exceed 10.5 hours in total (which is the total hours in a shift).
So let's say for example I would have:
UP=4 hours entered DOWN=2.25 hours entered the number in IDLE would automatically change to 4.25
Is there a way to configure this in Microsoft excel for this to work.

I know that I could add another cell and call it TOTAL then add the 3 cells above to be equal to 10.5 in the TOTAL cell and change color let's say RED if it doesn't add up to 10.5 and GREEN if it does equal to 10.5 but is there a way to make this work the way they really want it to work?
=b3+c3+d3 would use conditional formatting, then equal to 10.5 then change to GREEN and if not = 10.5 change to RED.

Thanks for any help with this.

[ibanez7]

Thanks for all the help but I got it done.

[jeffreybrown]

Glad you got it working and thanks for the follow-up

If you are satisfied with the answers provided, please do not forget to mark the thread as solved.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save