Color coding conditions

[JuanDSantos]

Hello,

I am needing to color code based on two conditions. On this base, I have various fields, one of them being a multiple record selection for tutoring subjects required. From here you can select up to 5 subjects. Now I have various other fields, these have the hours that they have been tutored per subject, some can say 0 since they do not require any tutoring in those subjects.

Now for the conditioned color coding, depending on the subjects they need tutoring in, I need them to meet 30 hours per subject. So if they meet 30 hours in the subject they require tutoring in I want them to turn green. Here is the tricky part, they can meet 30 hours in one subject that they require but, only 15 in the other, that would yield to the record not being marked as green since they only met 30 hours for one but not the other.

Example:
Student A requires tutoring in 2 subjects, she met 33 hours in one of the subjects but only 12 on the other subject this record would not be marked green.

Student B requires tutoring in 3 subjects she met 32 hours in the first subject, 30 hours in the second, and 36 in the third. This means this record would then be marked green.

What can I do about this?

[TheTimeSavingCo]

Hi Juan, I’d make a formula field to do the check for me and use that field for the conditioning:

And the formula used was:

IF(
  AND(
    FIND("Subject 1", Tags),
    {Subject 1 hours} >= 30
  ),
  "Pass",
  "Fail"
) & 
IF(
  AND(
    FIND("Subject 2", Tags),
    {Subject 2 hours} >= 30
  ),
  "Pass",
  "Fail"
) 

[JuanDSantos]

Adam,

Thank you so much for your response!

You see we have 9 subjects in total on the database, when expanding the formula you provided to reach all 9 subjects, obviously, there are 'Fail’s everywhere. Since not one student can take all 9 subjects, when we finally get to the conditioning for the color it is always going to find at least one ‘Fail’.

How can we work around this? Maybe eliminating the output of the word “Fail”?

[TheTimeSavingCo]

Ah, so sorry, try this:

IF(
  FIND("Subject 1", Tags),
  IF(
    {Subject 1 hours} >= 30,
    "Pass",
    "Fail"
  )
) & 
IF(
  FIND("Subject 2", Tags),
  IF(
    {Subject 2 hours} >= 30,
    "Pass",
    "Fail"
  )
)