Need help creating a formula

[glock35ipsc]

First off, I'm an Excel novice! I'm rebuilding a spreadsheet to track training that was built by a previous employee who is no longer here. And now I need to add a sheet to track hours, and I'm utterly confused on how to do it.

I can't post a picture, so hopefully I can adequately explain what I'm trying to do.

Let's just say everything is on Row 1......

The data in F1, H1, J1, L1, and N1 will simply be a one or two digit number to reflect the number of hours for a particular training.

C1 tracks the running total of the above columns in a given row, and in it, I'm using =SUM(F1,H1,J1,L1,N1)

C1 is the one I need help with. The main thing I would like it to be used for is to show if the person is in compliance with the number of training hours they are required to have in a calendar year (12).

The other thing I would like for it to do (if it's even possible) is for the cell to be red is the number in C1 is 11 or below. If it is 12 or above, then it would simply have no color fill.


I hope the above makes sense. There are formulas on another sheet that does basically the same thing, but with dates and shows compliance based an the number of days between training. I've tried to make it work for the new sheet, but I'm at a loss.

If there is any other info you need, please let me know and THANK YOU in advance!

 

[KennyGreens]

Seems redundant to have a separate cell say "yes" or "no" (or whatever you want it to say) based on if they are in compliance with the required hours when you will be formatting the cell that tracks the hours to show if they are in compliance anyway.

However, if you wanted to do both, here is how I would:

In cell D1 (or wherever you want it to say if they are in compliance) would be this formula: =IF(C1<12, "not in compliance", "yes, in compliance"), which basically says that if the sum in C1 is less than 12, they are not in compliance, if if is not less than 12 they are in compliance.

To format C1 as red:

select cell C1, and click "Conditional formatting" on the top ribbon. Select "Manage rules" and a pop up will appear. Click "new rule..." and then click "Format only cells that contain". Change the second drop down option to "less than" and type 12 in the last field. Click the format button next to the preview, click fill, and choose the color you want the cell to be if it is less than 12. Click OK and then Apply.

 

[glock35ipsc]

This did exactly what I was hoping for, thank you! This is not just for me to use, but for an agency of almost 200 to view. So it needs to be dead stupid easy to understand, which is why I wanted both. Thanks again!